> as you advised a few days ago
i'm sorry, i probably misled you
i said "CONCAT defaults to comma-separated"
i was flat out wrong
sorry, it happens occasionally (and still too frequently for my liking)
CONCAT does not default to comma-separated, it uses *no* separator
then i suggested
> CONCAT_WS "with separator" lets you specify the character to use, and in
my
> example, the character was empty, i.e. no character --
>
> concat_ws(''
> , coalesce(col1,'')
> , coalesce(col2,'')
> , coalesce(col3,'')
> )
this is still correct, although the COALESCEs may not be necessary
there are two additional benefits of using CONCAT_WS over CONCAT
-- nulls are automatically skipped!
-- you can use a string of length greater than 1, e.g. comma space
here's the sample -- tested, this time, so i know it works
create table smilepoet
( id tinyint not null primary key auto_increment
, cust_name varchar(20)
, cust_address varchar(20)
, cust_city varchar(20)
, cust_state varchar(2)
, cust_zip varchar(10)
, cust_country varchar(10)
);
insert
into smilepoet
( cust_name
, cust_address
, cust_city
, cust_state
, cust_zip
, cust_country )
values
('curly','123 main st','eureka','KS','12345','USA')
,('larry','456 pine st','nyawk','NY','10101-0101','USA')
,('moe','789 sesame st','Columbus','OH','43333','USA')
,('tom','123 main st',NULL,'KS','12345','USA')
,('dick','456 pine st','nyawk',NULL,'10101-0101','USA')
,('harry','789 sesame st','Columbus','OH',NULL,'USA')
select cust_name
, cust_address AS cust_address1
, concat_ws(', '
, cust_city
, cust_state
, cust_zip
) AS cust_address2
, cust_country AS cust_address3
from smilepoet
cust_name cust_address1 cust_address2 cust_address3
curly 123 main st eureka, KS, 12345 USA
larry 456 pine st nyawk, NY, 10101-0101 USA
moe 789 sesame st Columbus, OH, 43333 USA
tom 123 main st KS, 12345 USA
dick 456 pine st nyawk, 10101-0101 USA
harry 789 sesame st Columbus, OH USA
rudy
____ � The WDVL Discussion List from WDVL.COM � ____
To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED]
Send Your Posts To: [EMAIL PROTECTED]
To set a personal password send an email to [EMAIL PROTECTED] with the words: "set
WDVLTALK pw=yourpassword" in the body of the email.
To change subscription settings to the wdvltalk digest version:
http://wdvl.internet.com/WDVL/Forum/#sub
________________ http://www.wdvl.com _______________________
You are currently subscribed to wdvltalk as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
To unsubscribe via postal mail, please contact us at:
Jupitermedia Corp.
Attn: Discussion List Management
475 Park Avenue South
New York, NY 10016
Please include the email address which you have been contacted with.
