Is it possible to determine if a string is numeric characters followed by alphabetic characters?
E.g. "123AB" is valid, "4.5 CD" is also valid, "CDE3" is not. "4d9" is invalid.
declare @t table (my_string varchar(10))
insert @t
select '1.23bc' union -- Valid
select '4 d' union -- Valid
select 'a1' -- Invalid
select 'my_magic_expression'
from @t
Desired output:
This isn't particularly easily in T-SQL; it doesn't have support for Regex, only redimentary pattern matching. That being said you could achieve these by checking the following "rules" are true:
This gives something like the following:
DECLARE @t table (MyString varchar(10));
INSERT INTO @t (MyString)
VALUES('123AB'),
('45 CD'),
('3CDE'),
('123bc'),
('4 d'),
('CDE3'),
('4d9'),
('a1');
SELECT CASE WHEN MyString LIKE '[0-9]%[A-z]'
AND MyString NOT LIKE '%[^0-9 A-z]%'
AND STUFF(MyString,1,PATINDEX('%[^0-9]',MyString),'') NOT LIKE '%[0-9]%' THEN 1 ELSE 0
END,
MyString
FROM @t;
The code solution was written before the comments which denote that more than number and letter characters are valid. This answer is based on the post at the time the answer was written, which is likely to now change.