Allow one random character at any position in string pattern

I want to find a specific pattern in a string with the allowance of one (or a number I can set) tolerance deviation character at any position in the string.

So if I am looking for a date in the Format yyyy-mm-dd. Then I would like to accept for example:

‘2020-08x-12’

‘x2020-08-01’

‘2020-08-x12’

So far I’ve got the Standard pattern recognition:

SELECT foo FROM bar WHERE foo LIKE '[1-2][0-9][0-9][0-9][-][0-1][0-9][-][0-3][0-9]' 

Now I would like to allow a random character in between (max 1 character) and still recognize the pattern.

Asked on September 1, 2020 in Sql.
Add Comment
1 Answer(s)

SQL Server is not optimal for this but you can use a massive OR and LIKE:

WHERE foo LIKE '[1-2][0-9][0-9][0-9][-][0-1][0-9][-][0-3][0-9]' OR       foo LIKE '[1-2][0-9][0-9][0-9][-][0-1][0-9][-][0-3][0-9]_' OR       foo LIKE '[1-2][0-9][0-9][0-9][-][0-1][0-9][-][0-3]_[0-9]' OR       foo LIKE '[1-2][0-9][0-9][0-9][-][0-1][0-9][-]_[0-3][0-9]' OR       . . .       foo LIKE '_[1-2][0-9][0-9][0-9][-][0-1][0-9][-][0-3][0-9]' 

The _ matches exactly one character. So the idea is to put it in the pattern at every possible position.

Hmmm . . . an alternative method that should work is to match with any "random" characters between the known ones and then check the length:

WHERE foo LIKE '%[1-2]%[0-9]%[0-9]%[0-9]%[-]%[0-1]%[0-9]%[-]%[0-3]%[0-9]%' AND       LEN(foo) IN (10, 11) 
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.