Search code examples
sqlsql-serverregexsql-server-2017

sql repeat regex pattern unlimited times

I need to select where column contains numbers only and ends with a hyphen

I'm running SQL Server Management Studio v17.9.1

I have tried:

select * from [table] where [column] like '[0-9]*-'
select * from [table] where [column] like '[0-9]{1,}-'
select * from [table] where [column] like '[0-9]{1,2}-'

none of these work. The expression ([0-9]*-) works in any regex tester I've run it against, SQL just doesn't like it, nor the other variations I've found searching.

Solution

  • You can filter where any but the last character are not numbers and the last is a dash. DATALENGTH/2 assumes NVARCHAR type. If you're using VARCHAR, just use DATALENGTH

    SELECT
    * 
FROM 
    [table]
WHERE 
    [column] like '%-'
    AND
    LEFT([column], (datalength([column])/2)-1) NOT LIKE '%[^0-9]%'