LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices


Reply
  Search this Thread
Old 06-22-2008, 08:55 AM   #1
tanveer
Member
 
Registered: Feb 2004
Location: e@rth
Distribution: RHEL-3/4/5,Gloria,opensolaris
Posts: 525

Rep: Reputation: 39
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?
 
Old 06-22-2008, 08:21 PM   #2
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Rocky 9.2
Posts: 18,348

Rep: Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749
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."
 
Old 06-24-2008, 09:41 AM   #3
tanveer
Member
 
Registered: Feb 2004
Location: e@rth
Distribution: RHEL-3/4/5,Gloria,opensolaris
Posts: 525

Original Poster
Rep: Reputation: 39
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.
 
Old 06-24-2008, 06:17 PM   #4
nitehawk
LQ Newbie
 
Registered: Jun 2008
Distribution: Slackware
Posts: 11

Rep: Reputation: 0
Quote:
Originally Posted by tanveer View Post
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;

-
 
Old 06-24-2008, 10:44 PM   #5
tanveer
Member
 
Registered: Feb 2004
Location: e@rth
Distribution: RHEL-3/4/5,Gloria,opensolaris
Posts: 525

Original Poster
Rep: Reputation: 39
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?

Last edited by tanveer; 06-24-2008 at 10:46 PM.
 
Old 06-25-2008, 01:01 AM   #6
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Rocky 9.2
Posts: 18,348

Rep: Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749Reputation: 2749
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
 
Old 06-25-2008, 02:16 PM   #7
nitehawk
LQ Newbie
 
Registered: Jun 2008
Distribution: Slackware
Posts: 11

Rep: Reputation: 0
Quote:
Originally Posted by tanveer View Post
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.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
STL String: Uninitialized or some null value Ephracis Programming 9 03-04-2008 01:27 PM
Concat string (not ended ...) os2 Programming 2 03-25-2005 03:23 PM
php mysql insert null value spoody_goon Programming 2 03-28-2004 10:20 AM
java test if string in string array is null. exodist Programming 3 02-21-2004 01:39 PM
NullLogic Null Webmail Format String Vulnerability Aivukazz Linux - Security 1 10-09-2002 02:47 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Server

All times are GMT -5. The time now is 04:45 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration