Convert Float to Varchar

Question

Convert Float to Varchar

briandhulse

SSC Journeyman

Points: 79
More actions
May 14, 2008 at 7:54 pm

#71267

A client is providing me their data in a SQL-Server 2000 view. This view comes from a linked Oracle (v9?) database. The manifest numbers in the Oracle table (and the view) are floats, while the field in the SQL Server 2000 table receiving the date is a string (varchar 18). I converted it pretty quickly, but it looks ugly. Is there a better way? Here's what I've done:
select top 5 Manifest from vwORA_Table
where Manifest > 0
Result:
638603000.0
486385000.0
100441000.0
124213000.0
5536000.0
(as Floats)
select top 5 LTRIM(SubString(STR(Manifest,10,0),1,7)) as MyString from vwORA_Table
where Manifest > 0
Result:
638603
486385
100441
124213
5536
(as VarChar)
This client will transfer about 32K records one time each month (actually, they may do this 3-4 times after month end closing, checking the results in SQL Server and correcting on the Oracle system).
Thanks in advance,
Brian

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Why use substring and LTRIM? Shouldn't STR give you the string representation you need?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004649 More actions · Answer 2

Better question to ask is why is the column in your table VARCHAR(18) instead of FLOAT like it should be?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)