CODEWITHSUNDEEP
sqlsql-server
James Khan
James Khan

Reputation: 841

Best Sql Data Exception check REGEX

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 ?

Upvotes: 3

Views: 124

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81990

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'

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522346

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

Upvotes: 2

Related Questions