LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Server (https://www.linuxquestions.org/questions/linux-server-73/)
-   -   Concat string will NULL in mysql (https://www.linuxquestions.org/questions/linux-server-73/concat-string-will-null-in-mysql-650876/)

tanveer 06-22-2008 08:55 AM
Concat string will NULL in mysql
 
Hi all,

I want to know is there any way to concat a string with NULL as in a table of my database there are some fields with "NULL" and I want to add some string like "Abc" with in all rows in that field like

update tablename set post=CONCAT(post,"","Job") where id=234;

Here id is primary key and now after running this if any row contains NULL in that post field then this update returns NULL otherwise ok.

Now how to add string with NULL?

chrism01 06-22-2008 08:21 PM
Your qn is unclear.
SQL NULL is not a value, its 'flag' in the DB stating that there is no value (not a value) in that field.
You cannot concatenate onto or with a NULL, its a meaningless statement.

http://dev.mysql.com/doc/refman/5.0/...unction_concat "CONCAT() returns NULL if any argument is NULL."

tanveer 06-24-2008 09:41 AM
Hi thanks.
After posting here I too found that on mysql site after searching and As you said any operation with NULL returns NULL. Anyway, I wrote a php script to find out which records contained NULL value in that particular field and made update without concat function.

nitehawk 06-24-2008 06:17 PM
Quote:
Originally Posted by tanveer (Post 3191778)
Hi all,

I want to know is there any way to concat a string with NULL as in a table of my database there are some fields with "NULL" and I want to add some string like "Abc" with in all rows in that field like

update tablename set post=CONCAT(post,"","Job") where id=234;

Here id is primary key and now after running this if any row contains NULL in that post field then this update returns NULL otherwise ok.

Now how to add string with NULL?
I see that you have solved your problem however if others are looking for a solution, I would just use a simple if statement.

update tablename set post=if(post is null,"Job",CONCAT(post,"","Job")) where id=234;

- :)

tanveer 06-24-2008 10:44 PM
Great. I didn't know that. So many things yet to learn.

I did with the command below as post field is null anyway. Wrote a php code to find which records post field is null and then
Code:
update tablename set post="job" where id=234;
One more thing, will your command work with mysql server version 4.1.7-standard?

chrism01 06-25-2008 01:01 AM
Doc page for v3.23,4.0,4.1: http://dev.mysql.com/doc/refman/4.1/...ml#function_if


do read the Red boxed text here: http://dev.mysql.com/doc/refman/4.1/en/index.html

nitehawk 06-25-2008 02:16 PM
Quote:
Originally Posted by tanveer (Post 3194214)
Great. I didn't know that. So many things yet to learn.

I did with the command below as post field is null anyway. Wrote a php code to find which records post field is null and then
Code:
update tablename set post="job" where id=234;
One more thing, will your command work with mysql server version 4.1.7-standard?
I see Chris indirectly answered you, yes.
The IF statement is useful in many situations, as well as UNION and DUAL.

My Tip of the day:
I use the UNION when I am searching large tables on two keys, it's considerably faster.

Consider:
Code:
select * from MyTransactions
where Key1=123 or Key2=123
order by TranDate;
If the table is large, this will run MUCH faster:
Code:
( (select * from MyTransactions where Key1=123)
UNION
(select * from MyTransactions where Key2=123) )
as MyFewTransactions
order by TranDate;
Where Key1 may be such thing as a UserID and Key2 may be such thing as SupervisorID.


All times are GMT -5. The time now is 10:34 AM.