SQL Server - How to validate number using regex?

Question

I have encountered a problem with regex in SQL Server.

I have a query like this:

SELECT 1 
WHERE '/test/url/1' LIKE '/test/url/%[0-9]'

but there is a problem in this regex.

When I enter a non-valid URL like /test/url/1w1, it returns 1 and regex does not work correctly.

I want to validate an integer in URL with any length as :ID parameter.

Please recommend a correct regex for this situation.

sql-server weak for regex.

D-Shih
– D-Shih

2019-04-13 07:14:40 +00:00
Commented Apr 13, 2019 at 7:14 — D-Shih
– D-Shih, Commented Apr 13, 2019 at 7:14
SQL SERVER 2017 14.0.1000.169

Moein Hosseini
– Moein Hosseini

2019-04-13 07:16:41 +00:00
Commented Apr 13, 2019 at 7:16 — Moein Hosseini
– Moein Hosseini, Commented Apr 13, 2019 at 7:16

Gordon Linoff · Accepted Answer · 2019-04-13 12:24:04Z

1

You can use:

SELECT 1
WHERE '/test/url/1' LIKE '/test/url/%[0-9]' AND
      '/test/url/1' NOT LIKE '/test/url/%[^0-9]%'

This requires an integer (0-9) at the beginning of the last path-segment but avoids that the segment ends with an integer.

edited Apr 13, 2019 at 12:24

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

answered Apr 13, 2019 at 7:31

sabhari karthik

1,3818 silver badges18 bronze badges

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

3 Comments

Gordon Linoff Over a year ago

This is not a valid SQL expression. Why was it accepted as the answer (and upvoted)?

sabhari karthik Over a year ago

I am not sure Gordon. David edited this last and My answer was just "NOT LIKE '/test/url/%[^0-9]%'". Maybe the answer was accepted and upvoted before edit? Are you pointing out the missing 'And' condition here??

Manpreet Singh Dhillon Over a year ago

here is the reference to regex in SQL learn.microsoft.com/en-us/sql/ssms/scripting/…

Collectives™ on Stack Overflow

SQL Server - How to validate number using regex?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related