mysql concat

I am using the query below to update newstreet field however if there is a null record in the concat fields then the newstreet field is null. what can I do to carry on even if one of the fields are null.(Please mysql query only. no php)
thanks
update vaddress set newstreet = Concat(pre_dir, ' ', street, ' ', stype )
LVL 9
syedasimmeesaqAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roger BaklundCommented:
You could use coalesce:

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce
update vaddress set newstreet = Concat(coalesce(pre_dir,''), ' ', coalesce(street,''), ' ', coalesce(stype,'') )

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thinkingman2Commented:
use CONCAT_WS('separator',field1,field2,field3) like so:
update vaddress set newstreet = Concat_WS(' ',pre_dir, street, stype );

Open in new window

0
tcsaddul9Commented:
update vaddress set newstreet = IFNULL(Concat(pre_dir, ' ', street, ' ', stype ), '')
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

tcsaddul9Commented:
Or if you still want some to be retained:

update vaddress set newstreet = Concat(IFNULL(pre_dir,'<N/A>'), ' ', IFNULL(street,'<N/A>'), ' ', IFNULL(stype,'N/A'))

You use the string 'N/A' to signify that no such data inputted. In your program, you can detect such value and perform a task accordingly.
0
thinkingman2Commented:
The problem with IFNULL, etc, is that you have to define that for every field, and then you end up with very unclean output results (for example, your newstreet will be full of values like 'N/A Fifth Avenue' and '145 Fifth N/A' - the cleanest solution would be to first create a field on your table called 'flagged_incomplete' and run your queries like so:
update vaddress add `flagged_incomplete` int(1) default 0 BEFORE `newstreet`;
 
update vaddress set `flagged_incomplete` = NULL where (is_null(pre_dir) OR is_null(street) OR is_null(stype));
 
// now run the query with concat_ws:
 
update vaddress set newstreet = Concat_WS(' ',pre_dir, street, stype );
 
//now, at least you can find incomplete addresses by using:
 
select * from vaddress where flagged_incomplete = 1;

Open in new window

0
thinkingman2Commented:
(sorry the first query should read as follows)
ALTER TABLE vaddress ADD `flagged_incomplete` int(1) default 0 BEFORE `newstreet`;

Open in new window

0
tcsaddul9Commented:
A cleaner output would be:

update vaddress set newstreet = Concat(IFNULL(pre_dir,''), ' ', IFNULL(street,'''), ' ', IFNULL(stype,''))

You don't need to add another field just to check incomplete address.  The following can do the trick:

select * from vaddress where (pre_dir + street + stype) is null

or

select * from vaddress where pre_dir is null or street is null or stype is null
0
snoyes_jwCommented:
The CONCAT_WS() that thinkingman2 showed is the cleanest approach; it avoids repeating the separator for each argument, and it quietly skips any arguments that are NULL.
0
thinkingman2Commented:
hmm... ok, but i'm not sure why using coalesce would be better in this case? anybody have an answer to that?
0
Roger BaklundCommented:
I don't think it is better... but it is good enough: it gets the job done. Maybe the asker just started from the top and accepted the first working solution.
0
thinkingman2Commented:
makes sense!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.