Check valid decimal and integer values using TSQL

Question

Hi All,

I have a following requirement,

1. Check for valid integer in column "IntCol"

2. Check for Valid decimal in Column "DecCol"

I have checked google/ BOL, and found ISnumeric doesn't work as expected, meaning it may return false positives. Luckily found some code which evaluates the valid int and dec column with some workarounds. the sample data and ddl as follows

Declare @table table (IntCol nvarchar(6),DecCol nvarchar(6))
insert into @table
select '10','10.45' union all
select '10.00','10.+0' union all
select '45,23','10,478' union all
select '48.7','78.8.5' union all
select '1','14.0' union all
select '0','10.788' union all
select '100','45.89' 

--select * from @table

--check for valid integer column

select IntCol from @table
where ISnumeric(IntCol+'.0e0') =1

/****Output*****
IntCol
10
1
0
100
*/

--Check for valid Decimal Column

select DecCol from @table
where ISnumeric(DecCol+'e0') =1

/****Output*****
DecCol
10.45
14.0
10.788
45.89
*/

The above code works as expected, it filters out the valid integer and decimal values. But wanted to know what happens and how isnumeric works when concatenating those exponential values (e0 and .0eo).

If someone can explain what process happens behind this concatenation, it would be useful for me to understand the code

Thanks in Advance

---

Regards, Dineshkumar,
Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

Answer 1

First: on SQL 2012 you can use try_convert which returns NULL if the value does not convert.

Then on to your question. I will have to admit that this is a neat little trick that I have never seen before. Or thought of. And since I have never seen it before, I don't want to vouch for that it is foolproof. But it looks promising.

The reason it works is that E0 is floating point notation. Floating-point numbers are often express in string format with a decimal mantissa and an exponent of 10. For instance, 2.6788E2 is the same as 267.88. 2.6788E-2 is 0.026788.

If you have a valid decimal number in a string, adding E0 at the end should give a valid float. But if the string is already a float value or a money value with a leading $ character, isnumeric will return 1.

The int check works the same way: by adding .0E0 the string becomes invalid if there already is a decimal point.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

It is worth adding that this trick will not trap overflows. For instance isnumeric('1234567890123' + '.0E0') returns 1, but the value will not convert to int.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

Thanks Erland, for your explanation on this process. I feel better than before in understanding this particular piece of code

Do we have any other alternate way to find the valid decimal and integer in SQL Server 2008 ?

---

Regards, Dineshkumar,
Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

Answer 4

For non-negative integers you can use

col NOT LIKE '%[^0-9]%'

---

David Wilkinson | Visual C++ MVP

Answer 5

For non-negative integers you can use

col NOT LIKE '%[^0-9]%'

And this post was not made by David Wilkinson, but yours truly Erland Sommarskog. There are some problems with the NNTP bridge, and this post is also likely to be misattributed as well.

(Use the web UI to post? I dont' know how you people stand it, but I don't.)

---

Erland Sommarskog, SQL Server MVP, [e-mail]