SQL using concat
Hi,
I'm trying to use concat to bring two strings together in one column using MySql. It's working fine, but I'd like to insert a comma between the fields and I'm not sure how to do that.
Here is what I have
select concat(city,state) as location from address_table
The result is something like this
ConcordNH
BangorME
What I want the result to look like is
Concord,NH
Bangor,ME
Can someone help out with that please?
Thanks,
Nacht
I'm trying to use concat to bring two strings together in one column using MySql. It's working fine, but I'd like to insert a comma between the fields and I'm not sure how to do that.
Here is what I have
select concat(city,state) as location from address_table
The result is something like this
ConcordNH
BangorME
What I want the result to look like is
Concord,NH
Bangor,ME
Can someone help out with that please?
Thanks,
Nacht
PortletPaul
select concat(city,',',state) as location from address_table
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
a alternative method is to use + operator shown as below
select city + ',' + state AS location from address_tableASKER
Thanks guys, that worked. I wasn't adding in the third comma. Still don't really understand why there is a third comma (just before state).
>> Still don't really understand why there is a third comma
because you asked for it :)
City has no comma
State has no comma
You want a comma between City and State
= you need to add a comma
when using the concat() function you need to separate parameters by comma
select('Flintstone','Fred'
select('Flintstone', ', ', 'Fred') = Flintestone, Fred
', ' in the middle is a parameter, it just happens to contain a comma
another alternative could be:
set @separator := ', '
select concat(city,@separator,sta
it probably is easier to understand using the + operator as shown above where you would avoid possible comma confusion
because you asked for it :)
City has no comma
State has no comma
You want a comma between City and State
= you need to add a comma
when using the concat() function you need to separate parameters by comma
select('Flintstone','Fred'
select('Flintstone', ', ', 'Fred') = Flintestone, Fred
', ' in the middle is a parameter, it just happens to contain a comma
another alternative could be:
set @separator := ', '
select concat(city,@separator,sta
it probably is easier to understand using the + operator as shown above where you would avoid possible comma confusion
ASKER
Ahh... thanks Paul. I get it now.