convert narchar numeric handle, space,0, decimal

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

General SQL Server Forums

New to SQL Server Programming

convert narchar numeric handle, space,0, decimal

Author

Topic

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-08 : 21:40:58

How to edit this query can handle data conversion from nvarchar to numeric include data of 0, decimal, blank space record ?

Anly_code Nvarchar (25)
Data sample
1.2
0
blank record
space
3

This query is not work , how to edit query it can handle 1.2, space , 0 and blank record data ?
=====================================================
case dbo.item.anly_code25 when isnumeric(dbo.item.anly_code25) then convert(decimal(7,2),dbo.item.anly_code25) else dbo.item.anly_code25 end as xtl

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-09 : 09:06:35

there are a few problems here:

1. (I'm nitpicking!) you didn't post a query (starts with SELECT, ends with a semicolon).
2. You're trying to use the short-form CASE expression. If I expand it to the full form I get:


CASE
    WHEN dbo.item.anly_code25 = isnumeric(dbo.item.anly_code25) 
        THEN convert(decimal(7,2),dbo.item.anly_code25)
    ELSE dbo.item.anly_code25
END

But, the function ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0.

http://msdn.microsoft.com/en-us/library/ms186272.aspx

so in effect you are saying, "if anly_code25 = 1, convert it to decimal, otherwise leave it as is", which is not what you want, I think.

3. The CASE expression needs to resolve a datatype to return. This happens at compile time, not execution time. To resolve the datatype, it looks at the first return value. In your case this type is decimal(7,2). Then, at execution time, it hits the row with a space for anly_code25. This is not a decimal number, so the query fails.

Basically, you need to decide what the CASE expression should return if the source data is not numeric. It MUST return a number, because that's what the CASE statement figured out for the return data type.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources