CODEWITHSUNDEEP
c#sql-server
Vivek Nuna
Vivek Nuna

Reputation: 1

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

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

Upvotes: 1

Views: 42

Answers (1)

Martin Smith
Martin Smith

Reputation: 453707

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.

Upvotes: 3

Related Questions