Search code examples
sqlsql-serverfiltersql-server-2019

Advanced SQL Server LIKE pattern

I am working with a SQL Server 2019 machine, and a single table that is a list of file names and other details about the file. There are over 2 million rows in this table.

We are using a .NET framework 4.6 app to query this table. We want to filter the rows based on this pattern but I can't think of a way to write it. Even chatGPT can't seem to write it, but I feel like there is some way to do it that I'm missing. The problem i keep having is that it doesn't seem that SQL Server has a way of looking for a character or not a character, kind of like it was optional.

The pattern:

  • Starts with three numbers either a dash, underscore, or nothing
  • Three more numbers
  • Either a dash, underscore, or nothing
  • Three more numbers
  • Then anything

Example entries that should work:

123-456789 somethnljdsflkjsdf.ext
123456789 newsomething.ext
123_456_789 jlkajsdfkl.ext

Here is what I've come up with so far:

SELECT * 
FROM [#testingTable] AS [tt] 
WHERE [tt].[TestingValue] LIKE '012[_-]%325[_-]%020[_-]%000'
Solution

  • I'd do something like this:

    SELECT *
FROM [#testingTable] AS [tt]
WHERE REPLACE(REPLACE([tt].[TestingValue], '-', ''), '_', '') LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'