Wednesday, August 4, 2010

Regular Expression Problem in T-SQL

I have one SQL Challenge for you:
A table has one column Code. Here is the sample data.
DECLARE @T TABLE(Code varchar(20))
INSERT @T VALUES
('STQ-309-A65'),('XYZ-999-A65'),
('AZZ-345-B66'),('CzA-123-C671'),
('GUP-999-C67'),('STQ-123-c67'),
('AtT-456-B66'),('ATT-000-B66'),
('AWT-101-A65'),('AUV-111-d68'),
('stq-007-c67'),('att-123-A97'),
('stq-777-c99'),('byz-789-d100'),
('stq-111-250'),('1at-p2a-149')

You need to filter the codes based on below conditions:
1. Code can be only 11 or 12 CHAR long.
2. First char must be a - s or A - S
3. Second char must be t - z to T - Z
4. Third char can be any char a - z or A - Z but not a digit.
5. Digit 4th and 8th must be "-"
6. Char 5th, 6th, and 7th must be a digit.
7. Char 5th should be non-zero digit.
8. Char 8th can be a - z but not a digit
9. Position 9th and 10th must be ASCCI value of 8th CHAR. If ASCII code is of three digit then 9th, 10th, and 11th position should be occupy by ASCII code.


Try to get the solution before checking my solution:

SELECT * FROM @T
WHERE Code LIKE '[A-S][T-Z][A-Z][-][1-9][0-9][0-9][-][A-Z]'+CAST(ASCII(SUBSTRING(Code,9,1)) as varchar(3))


No comments:

Post a Comment