4

I have a T-Sql statement that is getting the next available number from a table. but the column is of type nvarchar. It dost have to be a number, But i still need to be able to get the next available number

so when i run the sql i try to only get numbers

WHERE
ISNUMERIC(myCol) AND CAST(REPLACE(myCol, N'0', N'') AS int)

but i get error

Conversion failed when converting the nvarchar value '45D-1' to data type int.

So i ran just 

select ISNUMERIC('45D-1')

And to my surprise its true

I already wrote a replace for "." which makes sense, But why is "D" a number? And why cant sql cast it? And is there a function to really test if its a Int?

CMS
  • 3,657
  • 1
  • 27
  • 46
  • 1
    That is because IsNumeric does not determine the value can be an integer. It returns 1 when the value can be implicitly cast to ANY datatype that is roughly like a number. If you are one 2012+ you could utilize try_convert. Here is a great article about the horrendous shortcomings of IsNumeric. http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/ – Sean Lange Nov 30 '16 at 21:33
  • 1
    "Why is D a number?" Afaik SQL Server treats it interchangeably with E for some reason. `select cast('45D-1' as float)`. Looks like fortran uses D for that purpose. – Martin Smith Nov 30 '16 at 21:33
  • 2
    In Fortran `E` notation indicates single-precision and `D` is double-precision. It's curious that SQL Server is happy with `select Cast( '45D-1' as Float )`, but fails on `select 45D-1`. (_Incorrect syntax near '-'._) (Ref [Table 6.4](http://www.cs.uwm.edu/~cs151/Bacon/Lecture/HTML/ch06s09.html).) – HABO Dec 01 '16 at 04:57

5 Answers5

3

INT and NUMERIC isn't the same. ISNUMERIC is returning anything that could possibly be a numeric type (INT, BIGINT, DECIMAL, FLOAT, MONEY).

Example: SELECT ISNUMERIC('$')

This isn't an INT but returns true because it is something that is correctly formatted as MONEY. ISNUMERIC also works on scientific notations.... SELECT ISNUMERIC('1e4') would result to true. The point i'm making is don't trust ISNUMERIC out right without understanding what it is evaluating, or all the possibilities.

S3S
  • 24,809
  • 5
  • 26
  • 45
  • Could you please help me to understand, how/when 'D' is getting evaluated as numeric? Ex: select ISNUMERIC('4D1') returns 1 – Santhana Dec 05 '18 at 06:08
  • 1
    Scientific notation @Santhana. See *Examples and other notation* at https://en.wikipedia.org/wiki/Scientific_notation – S3S Dec 05 '18 at 14:20
1

If 2012+ you can use Try_Convert()

select Try_Convert(int,'45D-1')

Returns 

NULL
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • i like your answer but its an old database (2008) and i get 'Try_Convert' is not a recognized built-in function name. – CMS Nov 30 '16 at 21:31
1
select ISNUMERIC('45D-1' + '.e0')

Worked for me and also helped me on the "." issue

CMS
  • 3,657
  • 1
  • 27
  • 46
  • @GordonLinoff Only you would consider that. From this point on, I will need a DNA test proving that you are actually a human being. – John Cappelletti Nov 30 '16 at 21:34
1

You hit by chance one valid notation (I did not know this either):

SELECT CAST('45D-1' AS FLOAT) --4.5
SELECT CAST('45D-2' AS FLOAT) --0.45
SELECT CAST('45D-3' AS FLOAT) --0.045
SELECT CAST('45D+1' AS FLOAT) --450
SELECT CAST('45D+3' AS FLOAT) --45000

Produces the same results as

SELECT CAST('45e-1' AS FLOAT)
SELECT CAST('45e-2' AS FLOAT)
SELECT CAST('45e-3' AS FLOAT)
SELECT CAST('45e+1' AS FLOAT)
SELECT CAST('45e+3' AS FLOAT)

Obviously there is a scientific notation using the letter d in the same way as the more usual e.

Just add .0 to your string and ISNUMERIC will return no number

DECLARE @s VARCHAR(100)='45D-1';
SELECT ISNUMERIC(@s+'.0')

(Works only, if your numbers haven't got decimals already...)

UPDATE

You might use PATINDEX to search for any occurance of a character not 0-9:

DECLARE @s VARCHAR(100)='45D-1';
SELECT CASE WHEN PATINDEX('%[^0-9]%',@s)>0 THEN 'Nope!' ELSE 'Yeah!' END AS IsInt
Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114
0

T-SQL works differently from other languages. Evaluation depends on query plan. It's free to evaluate them in any order that it sees as the most effective.

Use derivated qry like:

select * 
from (
    select * 
    from tbl
    where isnumeric(myCol) = 1
    -- where yourfunction(myCol) = 1 -- where your function returns null on non-numeric
) t
where myCol = 8

In your funtion you have to remove .#$A-Z,+-... But in some cases you want sciencist notation with D or E. Fastest is CRL function for this purposes.

Deadsheep39
  • 561
  • 3
  • 16