how to verify a string format in SQL server

Question

I'm working on a data import using sql server stored procedure. the data format is like:

A7(5)B666

the '5' is a variable, it could be multiple digital #, i.e. 10, 123, ....

the '666' is a variable too. it is a text string, each char is '6', but could have any # of '6', i.e. 666666, 66, 6, 66666666666.

other part, A7()B are fixed.

Is there a simple way to validate this in SP? I could parse the string using substring, but it seems too complex since I have to verify the #. Is there a method similar to regular expression in sql server? can I use LIKE operator? I could not find how to use it other than in WHERE clause.

thanks

Answer 1

Try this

DECLARE @t TABLE(v VARCHAR(500))
INSERT INTO @t 
VALUES
     ('A7(5)B666'),
     ('A7(555)B66666'),
     ('A7(5ups)B666'),
     ('A7(5)B6ups66'),
     ('A7(5)B666  ')

SELECT v, 
    CASE WHEN SUBSTRING(v,CHARINDEX('(',v)+1,CHARINDEX(')',v)-CHARINDEX('(',v)-1) LIKE '%[^0-9]%' 
         THEN 0 
         ELSE 1 
    END isNumberInBrackets, 
    CASE WHEN SUBSTRING(v,CHARINDEX(')B',v)+2,DATALENGTH(v)-CHARINDEX('(',v)-1) LIKE '%[^0-9]%' 
         THEN 0 
         ELSE 1 
    END isNumberAfterB
FROM @t