SQL Server determines whether a string is a number

Sql2005 has a function isnumeric (expression) function: Returns 1 when expression is a number, otherwise returns 0. This is just a rookie-level solution, and most of the situation is more effective.

eg

Select IsNumeric ('123'-- The result is 1

However, this function has a drawback!

eg

 SELECT  ,IsNumeric('-') as '-'    --1,IsNumeric('+') as '+'    --1,IsNumeric('$') as '$'    --1,IsNumeric('.') as '.'    --1,IsNumeric(',') as ','    --1,IsNumeric('\') as '\'    --1,IsNumeric('2D3') as '2D3'--1,IsNumeric('1d1') as '1d1'--1,IsNumeric('1e1') as '1e1'--1,IsNumeric('D') as 'D'   --0

When a dollar sign, plus minus, comma, and other symbols, or D, e before and after the occurrence of a number, will also return 1, which is more headache. Punctuation is good to understand, why d,e this situation, really do not understand the intentions of Microsoft design.

Is there any good way to solve it? Of course, take a look below.

Method: wildcard character. High-force grid use.

Applicable scenario: 2005 and above (version before 2005 has not been tried, should also support)

 --returns 0-is a pure number (supports positive and negative numbers, decimal points) SELECT PATINDEX('%[^0-9|.| -|+]%','2.2')--returns 0 --returns 0-is a pure integerSelect PATINDEX('%[^0-9]%','2.2')--returns non 0

SQL Server determines whether a string is a number