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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Concatenate Script
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

easy_goer
Starting Member

21 Posts

Posted - 11/27/2013 :  11:37:01  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 11/27/2013 :  11:41:42  Show Profile  Reply with Quote
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
Go to Top of Page

easy_goer
Starting Member

21 Posts

Posted - 11/27/2013 :  11:50:37  Show Profile  Reply with Quote
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 11/27/2013 :  12:24:25  Show Profile  Reply with Quote
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
Go to Top of Page

easy_goer
Starting Member

21 Posts

Posted - 12/03/2013 :  13:04:54  Show Profile  Reply with Quote
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!
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 12/03/2013 :  13:25:24  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 12/04/2013 :  00:15:36  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000