Concatenate Script - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Concatenate Script

Author

Topic

easy_goer
Starting Member

21 Posts

Posted - 2013-11-27 : 11:37:01

Hello,

I am attempting the run the following script..

select 'Active|'+column1+'|'+column2
from dbo.table1
where column3 != 'abc'
and column3 != 'def'

but, I receive the following error.

"Error converting data type varchar to float"

Column1 is a FLOAT column and Column2 is a INT column. Active is just text that I'm inserting.

Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 11:41:42

you need to cast them to varchar before you do concatanation


select 'Active|'+ CAST(column1 AS varchar(20)) + '|' + CAST(column2 AS varchar(20))
from dbo.table1
where column3 != 'abc'
and column3 != 'def'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

easy_goer
Starting Member

21 Posts

Posted - 2013-11-27 : 11:50:37

Thanks. When I try that the results pulled from Column1 are not displaying correctly. So, instead of displaying with the number I expect (e.g. 1000000, 1000001, 1000002, etc.) it's displaying like this..

1.94744e+006, 1.94745e+006, etc. Does this make sense?

Thanks!

quote:
Originally posted by visakh16

you need to cast them to varchar before you do concatanation

select 'Active|'+ CAST(column1 AS varchar(20)) + '|' + CAST(column2 AS varchar(20))
from dbo.table1
where column3 != 'abc'
and column3 != 'def'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 12:24:25

quote:
Originally posted by easy_goer

Thanks. When I try that the results pulled from Column1 are not displaying correctly. So, instead of displaying with the number I expect (e.g. 1000000, 1000001, 1000002, etc.) it's displaying like this..

1.94744e+006, 1.94745e+006, etc. Does this make sense?

Thanks!

quote:
Originally posted by visakh16

you need to cast them to varchar before you do concatanation

select 'Active|'+ CAST(column1 AS varchar(20)) + '|' + CAST(column2 AS varchar(20))
from dbo.table1
where column3 != 'abc'
and column3 != 'def'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

thats because its declared as float
what about this?


select 'Active|'+ CAST(CAST(column1 AS decimal(38,18)) AS varchar(38)) + '|' + CAST(column2 AS varchar(20))
from dbo.table1
where column3 != 'abc'
and column3 != 'def'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

easy_goer
Starting Member

21 Posts

Posted - 2013-12-03 : 13:04:54

That got me really close. What I ended up doing is changing the decimal portion from decimal(38,18) to decimal(38,0). The reason is that I just need the whole number. I don't need a decimal point or anything after the decimal point. Is there any risk in using decimal(38,0)? So far, it seems to work great.

Thanks!

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-12-03 : 13:25:24

nothing much except the value after decimal point will be truncated :)

select CONVERT(numeric(38,18),12345678901234567890.123456789012345678)
select CONVERT(numeric(38,0),12345678901234567890.123456789012345678)

Cheers
MIK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-04 : 00:15:36

quote:
Originally posted by easy_goer

That got me really close. What I ended up doing is changing the decimal portion from decimal(38,18) to decimal(38,0). The reason is that I just need the whole number. I don't need a decimal point or anything after the decimal point. Is there any risk in using decimal(38,0)? So far, it seems to work great.

Thanks!

why not use int then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources