• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 604
  • 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 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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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