Rudy,
Thank you. That worked superbly as you said. I had worked away at this
and then following Bj's suggestion, but had not gained clarity from the
manual. I think in testing with Bj's support I had used double quotes; it
seems the syntax here is single quotes. In the course of testing all the
oprions I think I dropped the separator itself! :-(
This is so far away from the SQL method that without help (at this stage of
learning certainly) there is almost no way of working it out. I didn't
grasp previously that the separator was the first term in quotes. It even
works with 'coalesce' in!
Well, it's painful, but I may be getting more understanding of how the code
works than if going through the book had been smooth. I console myself
with that, anyway.
Joseph
> > 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.
