How to check a string is numeric using Regex in SQL Server?

Question

IsNumeric allows below special characters

`SELECT  ISNUMERIC('10.50') as '10.50'
 ISNUMERIC('10,50') as '10,50'
 ,ISNUMERIC('-') as '-'
 ,ISNUMERIC('+') as '+'
 ,ISNUMERIC('$') as '$'
 ,ISNUMERIC('.') as '.'
 ,ISNUMERIC(',') as ','
 ,ISNUMERIC('\') as '\'`

So, I wanted to use RegEx https:// regexr.com/4gjhm to validate my numeric which must allow numbers and dot only.

Tried Regex ^[0-9]+[.]{0,1}[0-9]*$ with the help of PATINDEX, but didn't get the expected results

 `DECLARE @Amount As NVARCHAR(50) = '150';
  SELECT CASE 
            WHEN @Amount LIKE '%^[0-9]+[.]{0,1}[0-9]*$%'
            THEN 1
            ELSE 0
            END AS IsNumericResult`

Valid Ex: 10, 10.01, .20 Invalid Ex: 10.50.20, 10..20, etc

Is this possible to achieve through LIKE or PATINDEX in SQL Server 2017?

ISNUMERIC() won't help there as you already state, instead you can use TRY_CAST() / TRY_CONVERT(). Without the needs to Regex. — Ilyes
– Ilyes, Commented Jun 28, 2019 at 7:16
how about remove . and digits and then see inf there's anything left in the string... — Tony Hopkinson
– Tony Hopkinson, Commented Jun 28, 2019 at 7:35
What is the end goal here? Why are you validating that the string is of that pattern? Is it to detect if it will cast to numeric without error or some other reason? — Martin Smith
– Martin Smith, Commented Jun 28, 2019 at 7:58
The problem is, SQL Server does not support regular expressions. Whereas you can match single characters with a range [0-9] or set [01234] you can't use + or * to match an instictinct count of them. Ie something like [0-9]+ will match 1+ but not 123. So you might be best off with @Sami answer and add some additional tests to exclude for instance +123 or -1.5 — derpirscher
– derpirscher, Commented Jun 28, 2019 at 8:12

Ilyes · Accepted Answer · 2019-06-28 08:02:09Z

3

You can use TRY_CAST() or TRY_CONVERT() without the need to Regex expression

SELECT CASE WHEN TRY_CAST(@Amount AS INT) IS NULL THEN 0 ELSE 1 END IsNumericResult

You can change INT there to numeric or decimal as needed.

eg:

SELECT V Value,
       CASE WHEN TRY_CAST(V AS DECIMAL(10,2)) IS NULL
            THEN 0
            ELSE 1
       END IsNumericResult
FROM
(VALUES ('10.50'), ('+'), ('$'), ('-'), ('11')) T(V);

Here is a db<>fiddle to see how it's working.

edited Jun 28, 2019 at 8:02

answered Jun 28, 2019 at 7:19

Ilyes

15k5 gold badges31 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

derpirscher Over a year ago

This will also allow +10.5 and -11 which is not valid according to the op original question.

Serg Over a year ago

Try ` ('+0'), ('$0'), ('-0') ` the only difference with ISNUMERIC is ('$0')

Ilyes Over a year ago

@derpirscher 1st the OP doesn't state that in his Q, 2nd -11 isn't a number??? From where did that comes to your mind? There is no negative/positive numbers?

Martin Smith Over a year ago

For the specific example strings they gave this has the desired effect. rextester.com/VJX9909

derpirscher Over a year ago

@Sami citing from the op question: "validate my numeric which must allow numbers and dot only" Yes -11 is a number, but OP does not want to allow it, at least as I understand his question (and his inital regex which does not contain a negative sign)

Collectives™ on Stack Overflow

How to check a string is numeric using Regex in SQL Server?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related