sqlsql-serverregexsql-server-2016

SQL to tell if string is numeric characters followed by alphabetic characters


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:


Solution

  • 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:

    1. The string starts with a number and ends with a letter.
    2. The string only contains numbers, letters, and spaces.
    3. That the only characters that appear after the first non-number character are letters and spaces.

    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.