Solved

SQL using concat

Posted on 2013-06-02
6
596 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
FrontEnd tools to create web database application 7 54
Sql server insert 13 27
VB.net and sql server 4 33
Create snapshot on MSSQL 2012 3 17
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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 setup several different housekeeping processes for a SQL Server.

808 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