Find matching pattern

I have a table that contains multiple strings pattern (‘Q88 9WE’,’S9 D2P’,WC2R 3LS etc..). How do I extract and split the rows that meet only the pattern

AN NAA,
ANN NAA,
AAN NAA,
AANN NAA,
ANA NAA,
AANA NAA.

Where A is a character in the range A-Z and N is a digit 1-9

I have tried using the logic below and it doesn’t seem to work

SELECT case when len(a.Code) = 6 then
        case 
            when substring(a.code,1,1) LIKE '%[a-z]%' and  substring(a.code,2,1)  like substring(Code,PatIndex('%[0-9.-]%', Code),1) and substring(a.code,3,1) = '' 
             and substring(a.code,4,1)  like PatIndex('%[0-9.-]%', Code)
             then 1 
        end
      end as mapping , code
  FROM [dirtyTable] AS A

I’m trying to map each pattern to a number and I will later put in a cte so I can do a select * from cte where mapping = 1. I’m having difficulty creating a case logic for multiple digit scenario.

Answer

Same idea as Gordon’s post, but with a bit of code reuse to reduce chance of typos when entering a new pattern:

DECLARE @a CHAR(8) = '[a-zA-Z]' --can remove an a-z if not using a case-sensitive collation
    ,@n CHAR(5) = '[0-9]' --Can change to '[0-9.-]' as per OP's example if need to count these as numeric
    ,@s CHAR(1) = SPACE(1);
WITH [dirtyTable]
AS (
    SELECT code
    FROM (VALUES
             ('A1 1AA'  )
            ,('A11 1AA' )
            ,('AA1 1AA' )
            ,('AA11 1AA')
            ,('A1A 1AA' )
            ,('AA1A 1AA')
        ) t(code)
    )
SELECT code
FROM [dirtyTable]
WHERE code LIKE CONCAT(@a, @n, @s, @n, @a, @a          )
 OR string LIKE CONCAT(@a, @n, @n, @s, @n, @a ,@a      )
 OR string LIKE CONCAT(@a, @a, @n, @s, @n, @a ,@a      )
 OR string LIKE CONCAT(@a, @a, @n, @n, @s, @n ,@a , @a )
 OR string LIKE CONCAT(@a, @n, @a, @s, @n, @a ,@a      )
 OR string LIKE CONCAT(@a, @a, @n, @a, @s, @n ,@a , @a )

If you wanted to use your original approach, it wasn’t working for a couple of reasons:

1. One of your substrings was searching for an empty string (”) rather than a space (‘ ‘)
2. Your code was inconsistent: the first number search used PatIndex nested inside Substring (achieves desired result), but in the 2nd usage (looking at the 4th character), you missed off the Substring function (searches for something different, breaking the query).

With those errors corrected:

SELECT case when len(a.Code) = 6 then
        case 
            when substring(a.code,1,1) LIKE '%[a-z]%' 
                and substring(a.code,2,1) like substring(Code,PatIndex('%[0-9.-]%', Code),1)
                and substring(a.code,3,1) = ' '
                and substring(a.code,4,1) like substring(Code,PatIndex('%[0-9.-]%', Code),1)
             then 1 
        end
      end as mapping , code