Convert

[tlaksh]

I have a MSSQL 7.0 table with a varchar column that contains values like
0049
0320
E1000
V2000

etc.

I now need to view this value in a select statement as
49.0
320.0
E1000
V2000 etc.

Thanks for all ur help

Lakshmi

 

[balves]

Use a CASE statement with IsNumeric().

Something like this:

SELECT result =
CASE IsNumeric(fieldx)
WHEN 1 THEN Convert(Decimal(9,2), fieldx)
ELSE Convert(varchar(20), fieldx)
END

 

[tlaksh]

This is what i am trying

SELECT result =
CASE IsNumeric(substring(abbr,1,1))
WHEN 1 THEN Convert(Decimal(9,1), abbr)
ELSE Convert(varchar(20), abbr)
END
FROM icd9

It converts the numbers into decimal but does not display alpha numeric and displays the following error in the end:


Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Thanks
Lakshmi.

 

[balves]

Huh. Very weird. If you make both the WHEN and the ELSE a Convert(varchar(20), abbr) then no errors.

But when you make the 2 clauses Convert(Decimal() and Convert(varchar() it seems to do both conversions anyway and blows up.

 

[balves]

It has something to do with data type precedence, and having the different WHEN clauses return different data types.

This works, but it's ugly. Perhaps someone has a better idea. I hope so...

SELECT result =
CASE IsNumeric(substring(abbr,1,1))
WHEN 1 THEN Convert(varchar(20), Convert(Decimal(9,1), abbr))
ELSE Convert(varchar(20), abbr)
END
FROM icd9

 

[tlaksh]

Thanks a ton...u r a lifesaver...

Lakshmi