Solved

mysql concat

Posted on 2009-06-30
11
900 Views
Last Modified: 2012-05-07
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 )
0
Comment
Question by:syedasimmeesaq
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 500 total points
ID: 24748942
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
 
LVL 2

Expert Comment

by:thinkingman2
ID: 24750483
use CONCAT_WS('separator',field1,field2,field3) like so:
update vaddress set newstreet = Concat_WS(' ',pre_dir, street, stype );

Open in new window

0
 
LVL 3

Expert Comment

by:tcsaddul9
ID: 24751283
update vaddress set newstreet = IFNULL(Concat(pre_dir, ' ', street, ' ', stype ), '')
0
 
LVL 3

Expert Comment

by:tcsaddul9
ID: 24751294
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
 
LVL 2

Expert Comment

by:thinkingman2
ID: 24751314
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 2

Expert Comment

by:thinkingman2
ID: 24751317
(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
 
LVL 3

Expert Comment

by:tcsaddul9
ID: 24751488
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
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 24751505
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
 
LVL 2

Expert Comment

by:thinkingman2
ID: 24767116
hmm... ok, but i'm not sure why using coalesce would be better in this case? anybody have an answer to that?
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24767189
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
 
LVL 2

Expert Comment

by:thinkingman2
ID: 24767264
makes sense!
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PHP strip quotes and line feeds out of array 13 49
mySql Syntax 7 44
PHP - AJAX and MySQL it works only if the value is a number 12 38
change database name 2 4
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now