Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 603
  • Last Modified:

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
0
nachtmsk
Asked:
nachtmsk
  • 3
  • 2
1 Solution
 
PortletPaulCommented:
select concat(city,',',state) as location from address_table
0
 
PortletPaulCommented:
the comma is 'just another parameter' in the concat function

concat( city, ',' , state )

notice the single quotes, it might be easier to reconize it this way:

concat( city, ' any-string-here ' , state )

where that string just happens to be a comma

not you can add spaces either side of that added comma if needed- I leave that to you
0
 
Surendra NathCommented:
a alternative method is to use + operator shown as below

select city + ',' + state  AS location from address_table

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
nachtmskAuthor Commented:
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).
0
 
PortletPaulCommented:
>> 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') = FlintestoneFred

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,state) from yourtable

it probably is easier to understand using the + operator as shown above where you would avoid possible comma confusion
0
 
nachtmskAuthor Commented:
Ahh... thanks Paul. I get it now.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now