Best Sql Data Exception check REGEX

Question

I have a gas cert number column and want to check if any of them do not confor the format of a gas cert number . The format should be : WN/3333333/1 ( 2 LETTERS that should be WN followed by / and a 7 digit number then / and a single digit, like 1 and not 01 or 0001.

My thinking was to work out all the scenarios and solve them one by one. But the 1st step is working out what all the exceptions are by doing some super sql query .

Is there a good way of trying to identify the execptions using sql ?

Answer 1

For a dynamic pattern match consider the following:

"A" represents any ALPHA and the "0" represents any DIGIT.

Declare @Patt varchar(150) ='AA/0000000/0'
Declare @Test varchar(150) ='WN/3333333/1'

Set @Patt = replace(replace(replace(@Patt COLLATE Latin1_General_BIN, 'A', '[A-Z]'), 'a', '[a-z]'), '0', '[0-9]')
Select case when @Test Like @Patt then 1 else 0 end

Returns 1 or 0

1    -- Pattern Match

Now, if the pattern would always be WN/..., the @Patt would be 'WN/0000000/0'

Answer 2

SQL Server supports some basic regex functionality with LIKE. I think you can identify non conforming records by using a single LIKE expression.

SELECT column
FROM yourTable
WHERE column NOT LIKE 'WN/[0-9][0-9][0-9][0-9][0-9][0-9][0-9]/[0-9]'