Check if the string is following the correct format or not. The correct format is as follows:
2 upper case letters; 2 digits; 1 to 30 characters alpha-numerical (case insensitive) e.g. GB29RBOS60161331926819, GB29RBOS60161331926819A, GB29RBOS60161331926819B1
So far this is what i have got...
declare @accountNumber varchar(1000) = 'GB99AERF12FDG8AERF12FDG8AERF12FDG8'
select
case when @accountNumber not like '[A-Z][A-Z][0-9][0-9][0-9a-zA-Z]{30}$'
then 'ERROR' else null end
First, your structure assumes a case sensitive collation. Second, SQL Server doesn't recognize {} or $, so you have to repeat the pattern. However, you want up to 30 characters, so splitting the pieces apart is probably the best solution:
select (case when len(@accountNumber) not between 5 and 34 or
@accountNumber not like '[A-Z][A-Z][0-9][0-9]%' or
right(@accountNumber, 34) like '%[^A-Za-z0-9]%'
then 'ERROR'
end)
I think this should work... taking some tips from John.
declare @table table (i varchar(36))
insert into @table
values
('GR09xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'), --30 x's
('GR09xxxxxxxxxxxxxxxxxxxxxxxxxxxx'), --28 x's
('GR09xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'), --31 x's
('Gx09xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'), --lower case 2'd letter
('G509xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'), --digit second letter
('GRg9xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') --charcater first number (3rd index)
select
case
when i + replicate('a',case when 34-len(i) < 0 then 0 else 34-len(i) end) not like '[A-Z][A-Z][0-9][0-9]' + replicate('[a-zA-Z0-9]',30)
then 'ERROR' else null end
from @table
