有没有办法在SQL Server查询中编写正则表达式?

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]*%'

期望值:

Input
Input1
Input123

非预期值:

Inputabc
abc
<empty string>
abcInput
评论
  • Liz
    Liz 回复
    有没有办法在SQL Server查询中编写正则表达式?

    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.