Concatenation of 3 columns from a table

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

SQL Server 2005 Forums

Transact-SQL (2005)

Concatenation of 3 columns from a table

Author

Topic

mihaispr
Starting Member

3 Posts

Posted - 2009-11-11 : 17:09:55

Hi everyone and thanks for reading my question!

How I can concatenate 3 columns of a table.

I have 5 columns with 2 lines .

The thing I want is to concatenate the first 3 columns (the first column is int type ) the other 2 are nchar(30).

The idea is to convert my first column which is int to the same type so nchar(30).

I try this:


 
SELECT   CONVERT(int,CD_number)+ ' ' + CONVERT(nchar(30),CD_name)+ ' ' + CONVERT(nchar(30),Band_name)   AS  Informations
FROM      T_Stock

Error:


Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'Lost Highway                  ' TO DATA type int.

Desired output (concatenate the values for first 3 columns all the 2 lines in a single column called Informations):


Informations
 
1 Lost HighWay Bon Jovi
2 Cher Cher

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-11 : 17:18:36

You wanted to convert the cd_number to nchar but your statement is convert(int,cd_number)?

No, you're never too old to Yak'n'Roll if you're too young to die.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-11-11 : 17:20:30

And if you use nvarchar you won't get all the trailing spaces

Be One with the Optimizer
TG

mihaispr
Starting Member

3 Posts

Posted - 2009-11-11 : 17:32:55

quote:
Originally posted by webfred

You wanted to convert the cd_number to nchar but your statement is convert(int,cd_number)?

No, you're never too old to Yak'n'Roll if you're too young to die.

Thank you for your help!

It's working now!

Thanks!


SELECT   CONVERT(nchar,CD_number)+ ' ' + CONVERT(nchar(30),CD_name)
+ ' ' + CONVERT(nchar(30),Band_name)   As  Informations
FROM      T_Stock

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-12 : 01:39:14

quote:
Originally posted by mihaispr

quote:
Originally posted by webfred

You wanted to convert the cd_number to nchar but your statement is convert(int,cd_number)?

No, you're never too old to Yak'n'Roll if you're too young to die.

Thank you for your help!

It's working now!

Thanks!

SELECT CONVERT(nchar,CD_number)+ ' ' + CONVERT(nchar(30),CD_name)
+ ' ' + CONVERT(nchar(30),Band_name) As Informations
FROM T_Stock

You should always specify the length for the char datatypes(char,varchar,nchar,etc)
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources