Concatenate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Concatenate

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-01-20 : 15:32:02

I am trying to run a query that will concatenate fields from a table in my database. I know how to get it to work in excel but cannot figure it out in SQL - I am using SQL 2005. Can anyone help me?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-20 : 15:35:21

Here's an example:

SELECT Column1 + Column2
FROM Table1

This behaves differently for the various data types. For instance, it'll concatenate them if they are varchar or char. But it'll add them together for the number data types.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-20 : 15:36:51

Here's an example showing the differences between varchar and int:

declare @t1 table(column1 varchar(5), column2 varchar(5))

insert into @t1 values('1', '2')

select column1 + column2
from @t1

--returns string 12
go

declare @t1 table(column1 int, column2 int)

insert into @t1 values(1, 2)

select column1 + column2
from @t1

--returns number 3
go
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-20 : 15:38:13

Hello;

Here is a simple example of concatenation. You simply need to use the + operator.

Assume you have a TABLE A with two columns called region_name and store_name... For this example there is only 1 entry as shown

TABLE A

region_name store_name
eastern boston

SELECT region_name + ' ' + store_name FROM TABLE A

Result: easternboston

r&r

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 08:47:54

make sure you convert NULL values to '' before concatenation using COALESCE() or ISNULL() just in case any of fields involved are nullable, otherwise concatenation will yield you only NULL if you've CONCAT NULLS YIELDS NULL setting ON.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources