Solved

SQL using concat

Posted on 2013-06-02
6
597 Views
Last Modified: 2013-06-03
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
Comment
Question by:nachtmsk
  • 3
  • 2
6 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39215206
select concat(city,',',state) as location from address_table
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39215210
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
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39215264
a alternative method is to use + operator shown as below

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

Open in new window

0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:nachtmsk
ID: 39215265
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39215285
>> 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
 

Author Comment

by:nachtmsk
ID: 39215762
Ahh... thanks Paul. I get it now.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question