Best way to validate a number in SQL Server

Question

I know the newer versions have a try parse method similar to c# but unfortunately I'm on 2005. I know the knock on the built-in function to validate numbers is that it technically doesn't and many recommend creating one.

Is it acceptable and efficient to just use a try catch block with the catch block being considered false? ie

TRY
CONVERT(decimal(19,2),@value)
END TRY
GO
BEGIN CATCH
--false. Log error (or whatever you want to do)
END CATCH;
GO

ISNUMERIC is the buil-int function I referenced that isn't a true way to verify a string is numeric and most places I've worked always had to create their own version — user609926, Jul 01 '15 at 03:55
possible duplicate of [Checking for numeric validation](http://stackoverflow.com/questions/18428131/checking-for-numeric-validation) — Arunprasanth K V, Jul 01 '15 at 05:20

score 1 · Answer 1 · answered Jul 01 '15 at 05:01

1

Well, actually you can use negative like expression to check whether the value is is numeric or not. it is something like this

select case when numberAsString not like '%[^0-9]%' then 'true' else 'false' end from table t;

answered Jul 01 '15 at 05:01

kucing_terbang

4,991
2
22
28

score 0 · Answer 2 · answered Jul 01 '15 at 04:04

0

SQL2012:

DECLARE @Test TABLE (NumAsString VARCHAR(22))
INSERT  @Test VALUES ('12.3'), ('123.45'), ('123.'), ('123'), ('.4'), ('.45'), ('.'), (''), (null)

SELECT  t.NumAsString, 
  CASE 
    WHEN TRY_PARSE(t.NumAsString AS DECIMAL(19,2)) IS NOT NULL THEN 1
    ELSE 0
  END AS [is numeric(19,2)]
FROM    @Test t

SQL Fiddle

answered Jul 01 '15 at 04:04

Bogdan Sahlean

19,233
3
42
57

The question specifically states that the OP is on SQL Server 2005. – Damien_The_Unbeliever Jul 01 '15 at 06:44

Emipro Technologies Pvt. Ltd. · Answer 3 · 2015-07-01T11:51:39.707

ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. Valid numeric data types include the following:

int
numeric
bigint
money
smallint
smallmoney
tinyint
float
decimal
real

ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols, see money and smallmoney (Transact-SQL).

select ISNUMERIC(@Value)
select case when ISNUMERIC(@Value) == 1 then 'Numeric' else 'Non Numeric' end

You can convert string value to numeric or any other types.

TRY
    select CONVERT(numeric(18,2),@value)
END TRY
BEGIN CATCH
--false. Log error (or whatever you want to do)
END CATCH;
GO

Yes, `ISNUMERIC` answers the question that *nobody* has ever wanted to ask "can I convert this string to *any* of the numeric data types, I don't care which ones?" — Damien_The_Unbeliever, Jul 01 '15 at 06:43

Best way to validate a number in SQL Server

3 Answers3