valid record in SQL query

Question

I have a table with few columns and one of the column is DockNumber. I have to display the docknumbers if they confirm to a particular format

First five characters are numbers followed by a - and followed by 5 characters. The last but one character should be a alpha.

12345-678V9

How can I check in SQL if the first 5 characters are numbers and there is a hyphen and next 3 are numbers and last but one is an alpha.

Answer 1

Regular Expressions can be your friend.

Answer 2

LIKE '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][A-Z][0-9]'

Now, this allows lower case a-z too. You'd need to coerce collation if you wanted upper case only

Value COLLATE Latin_General_BIN
       LIKE '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][A-Z][0-9]' COLLATE Latin_General_BIN

Answer 3

PATINDEX is probably the ideal solution.

Select ...
From Table
Where PatIndex('[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][A-Z][0-9]', DockNumber) > 0

Answer 4

Use rule

CREATE RULE pattern_rule 
AS
@value LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][A-Z][0-9]'

Then bind rule to column

Answer 5

Where  DockNumber Like '[0-9][0-9][0-9][0-9][0-9][-][0-9][0-9][0-9][a-z][0-9]

should work, but i would suggest using Regular expression in code. Much easier if it is possible.

Answer 6

The regex should be '^\d{5}-\d{3}[A-Z]\d$', because without ^ and $ it would find longer strings that contain that sequence (122 12345-678V9 34).

Answer 7

you can use this, you will have to figure it out on how to use this...

SELECT Case when 
Cast(ISNUMERIC(LEFT(@Str,5)) as int) + case when substring(@str,6,1)= '-' then 1 else 0 end +case when substring(@str,10,1) like '[a-z]' then 1 else 0 end =3
THEN 'Matched'
Else 'NotMatched'
End

Answer 8

Building on @gbn's answer, this checks to make sure the length is 11 (in case the @val is not a char(11) or varchar(11) and also checks to make sure the second to last char is alpha

DECLARE @val VARCHAR(20)
SET @val = '12345-678V9'
SELECT  CASE WHEN LEN(@val) = 11 AND @val LIKE '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][A-Z0-9][0-9]' 
        THEN 'isMatch'
        ELSE 'isNotMatch'
    END AS [Valid]