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