ISNUMERIC not helping convert from nvarchar to real 2

[ookete]

I have an nvarchar column that is sometimes used to hold numeric data and I wish to pull that data out as real and changing text values to zero. So I wrote a query something like the following:

SELECT CASE WHEN ISNUMERIC(TextField)=1 THEN CAST(TextField AS real) ELSE 0 END
FROM MyTable

I am getting an error that states: Error converting data type nvarchar to real. I am finding that ISNUMERIC(TextField)=1 is still allowing commas, dashes by themselves, and who knows what else. For example: 1,5 and -. These apparently "numeric" values can't be converted to real. Can anyone tell me a test that is more conclusive than ISNUMERIC? I wish there was an ISREAL function...

Thank you!

 

[gmmastros]

SELECT CASE WHEN ISNUMERIC(TextField [!]+ 'e0'[/!])=1 THEN CAST(TextField AS real) ELSE 0 END
FROM MyTable

IsNumeric allows for some pretty strange things. There was a recent discussion in the VB forum lately regarding this same issue. It appears as though adding 'e0' to the end of the data before checking for isnumeric gives a better result.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I tested it like this...

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('a')
Insert Into @Temp Values('1234')
Insert Into @Temp Values('1e4')
Insert Into @Temp Values('-')
Insert Into @Temp Values('-1223')
Insert Into @Temp Values('+1223')
Insert Into @Temp Values('1223.293849234')
Insert Into @Temp Values('1,223')

Select Data,
       IsNumeric(Data) As IsNumeric,
       IsNumeric(Data [!]+ 'e0'[/!]) As BetterIsNumeric
From   @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

you have to use WHERE Value NOT LIKE '%[a-z]%'
AND ISNUMERIC(Value) = 1

check this out, 1d2 will be returned as numeric for example

CREATE TABLE #foo (Value VARCHAR(20))
INSERT INTO #foo
SELECT '1' UNION ALL
SELECT '3' UNION ALL
SELECT 'B' UNION ALL
SELECT '2' UNION ALL
SELECT '33.331' UNION ALL
SELECT 'adad1' UNION ALL
SELECT '1d2' UNION ALL
SELECT '^' UNION ALL
SELECT '17777.999'

--returns ^
SELECT * FROM #foo
WHERE Value NOT LIKE '%[a-z]%'

--returns 1d2
SELECT * FROM #foo
WHERE ISNUMERIC(Value) = 1

--returns correct result
SELECT * FROM #foo
WHERE Value NOT LIKE '%[a-z]%'
AND ISNUMERIC(Value) = 1

color version available here-->

http://sqlservercode.blogspot.com/2006/01/isnumeric-sql-server-trouble.html

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ookete]

gmmastros, Wow that's great! Who would have thought to make in scientific notation first, nice idea. Thanks!

SQLDenis, I was originally thinking along the same lines, but the NOT LIKE '%[a-z]%' does not catch the case of a single dash "-". This value still cannot be converted to real (but can be converted to money, which is why ISNUMERIC allows it).

 

[SQLDenis]

Have a star from me also George

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLBill]

You could do NOT LIKE '%[-a-z]%'

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

I forgot to add that doing a search with a leading % is normally a bad idea. That would ignore any indexes and make the query slower. So while SQLDenis' and my solution would work, George's should be MUCH faster.


-SQLBill

Posting advice: FAQ481-4875

 

[gmmastros]

Uh... thanks for stars. Kinda feels like cheating though because I just 'borrowed' an idea from a VB FAQ's. It's pretty cool that it works with SQL Server, also.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

That's the point George. You took an idea that worked elsewhere and realized it would also solve this poster's idea. Plus you taught us something new.

-SQLBill

Posting advice: FAQ481-4875

 

[ookete]

The only problem with the +'e0' is if the column does happen to contain scientific notation. The ISNUMERIC by itself would work great on a value of '1e3', but if you turned it into '1e3e0' then you've just invalidated your once-numeric value. I guess you just need to know your data. In my case, I am safe adding the 'e0' because I know that we aren't using that kind of notation in the column.

 

[gmmastros]

Right! This isn't a catch all solution. If you want scientific notation to be valid, then this solution is not for you.

I guess you just need to know your data.

I couldn't have said it better myself.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom