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
LVL 1
nachtmskAsked:
Who is Participating?
 
PaulConnect With a Mentor Commented:
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
 
PaulCommented:
select concat(city,',',state) as location from address_table
0
 
Surendra NathTechnology LeadCommented:
a alternative method is to use + operator shown as below

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

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
 
PaulCommented:
>> 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.