Is there a way to write Regex in SQL Server query?

Question

I am running a query on the SQL server. I need to get records where LoginName is starting with a given string and ending with integers. I am running the below query but it does not return any record. Let me know if this is not possible in SQL so I will handle in C# code.

SELECT * from TestTable where LoginName like 'Input[0-9]*%'

Expected values:

Input
Input1
Input123

Not Expected values:

Inputabc
abc
<empty string>
abcInput

SQL Server only supports basic pattern matching. If you require true Regex support, you'll need to look at CLR functions. — Thom A
– Thom A ♦, Commented Apr 24, 2020 at 11:06
As for why you get no rows, that is because none of them start with 'Input', followed by a digit, followed by an asterisk (*). None of your expect results have a '*' in them. Seems you actually just want LIKE 'Input[0-9]%'. — Thom A
– Thom A ♦, Commented Apr 24, 2020 at 11:09
@Larnu - * means "match zero to unlimited times" in regex syntax. So not valid in TSQL pattern — Martin Smith
– Martin Smith, Commented Apr 24, 2020 at 11:17
But, as I mentioned @MartinSmith , T-SQL does not support Regex, and LIKE will see * as a literal. Which is exactly why there are no rows, as I said too. — Thom A
– Thom A ♦, Commented Apr 24, 2020 at 11:19
yeah - I wasn't implying that you thought that * would work but your suggested Input[0-9] is not equivalent as that means "at least once" — Martin Smith
– Martin Smith, Commented Apr 24, 2020 at 11:25

Martin Smith · Accepted Answer · 2020-04-24 12:10:43Z

3

Is there a way to write Regex in SQL Server query?

Only by using CLR. There is no native support for regex. LIKE and PATINDEX support a very limited pattern matching dialect.

Nonetheless this is sufficient to get rows where LoginName is the string "Input" followed by any number of digits (including zero digits) then end of string.

As in the following (DBFiddle link)

SELECT *
FROM   TestTable
WHERE  LoginName LIKE 'Input%'
       AND SUBSTRING(LoginName, LEN('Input') + 1, 8000) NOT LIKE '%[^0-9]%'

LoginName LIKE 'Input%' - LoginName starts with "Input"
SUBSTRING(LoginName, LEN('Input') + 1, 8000) NOT LIKE '%[^0-9]%' - the substring after "Input" does not contain a character that is not in the range 0-9

If you change the string to search for from Input remember to update both instances in the query.

edited Apr 24, 2020 at 12:10

answered Apr 24, 2020 at 11:13

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

1 Comment

Vivek Nuna Over a year ago

Let us continue this discussion in chat.

Collectives™ on Stack Overflow

Is there a way to write Regex in SQL Server query?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related