Advanced SQL Server LIKE pattern

Question

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'

"I am working with a SQL Server 2019" then why tag [sql-server-2012]? Please make sure to tag correctly. — Thom A
– Thom A ♦, Commented Nov 8, 2024 at 18:35

Martin Smith · Accepted Answer · 2024-11-08 17:42:30Z

1

There is no syntax for optional characters from a set in the SQL Server pattern syntax.

Given that the underscore and dash checks can be combined you've only got 4 permutations so can just list them out

WHERE   TestingValue LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
     OR TestingValue LIKE '[0-9][0-9][0-9][_-][0-9][0-9][0-9][0-9][0-9][0-9]%'
     OR TestingValue LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][_-][0-9][0-9][0-9]%'
     OR TestingValue LIKE '[0-9][0-9][0-9][_-][0-9][0-9][0-9][_-][0-9][0-9][0-9]%'

answered Nov 8, 2024 at 17:42

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Iłya Bursov · Accepted Answer · 2024-11-08 19:42:49Z

0

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

edited Nov 8, 2024 at 19:42

answered Nov 8, 2024 at 17:42

Iłya Bursov

24.4k4 gold badges36 silver badges68 bronze badges

2 Comments

Martin Smith Over a year ago

Would also match -1_2-3456789 newsomething.ext as it just strips them out regardless of position

Chandler R Reeves Over a year ago

Thanks! That worked. I never thought about removing the extra characters. I think Martin's works as well, but i feel like this is the better because it will expand for us later if we add more conditions.

Collectives™ on Stack Overflow

Advanced SQL Server LIKE pattern

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related