Combine numbers and text into one column 1

[beanNut]

I was tasked with combining data from separate tables that contain text, date and numbers. The customer wants it in a single column. So I did a union of the three tables.

So I chose to union the 3 tables into a single column and cast each type to a universal type. For dates and text that was simple, all I needed to do was cast them into a varchar large enough to handle the data.

However, high precision numbers won't cast into varchar without losing precision. It rounds to 6 significant digits. Hence, 1.123456789123456789 becomes 1.123457.

Can someone suggest a solution?

SELECT
varfloat,
CAST(CAST(varfloat AS float(53)) AS float(53)),
CAST(CAST(varfloat AS varchar(250)) AS float(53))
from testCasting

I'm hoping that you can point me to the correct datatype to use has my union type. Here I cast to varchar(250), but it rounds the value to 1.123457. I think its because its single byte where float(53) was double byte. Is there a double byte data type I can use???? Please say yes..

 

[cjelec]

Your better off asking the people in the sql programming forum:

http://www.tek-tips.com/threadminder.cfm?pid=183

 

[BobRodes]

Well, you won't be able to do what you want using either the sql cast or sql convert functions. Cast limits float-to-char conversions to six digits, as you have found. Convert allows a style parameter that will allow 16 digits only. So:

select cast(cast(1.29834792834792834792837 as float)as varchar(250))

yields 1.29834, and

select convert(varchar(100), cast(1.29834792 as float),2)

yields the impossibly ugly 1.298347920000000e+000.

I would suggest that you pull the values into the .Net environment as a float and change them to a string at this end.

HTH

Bob

 

[earthandfire]

Bob, as I explained in my reply to beanNut on the SQL Server forum, it can easiily be done in TSQL using eithr CAST or CONVERT by using Decimal as an intermediary as in my reply to: thread183-1361972

Hope this helps.

 

[BobRodes]

Very nice, earthandfire, I stand corrected.

Bob