how to check for a string pattern in SQL Server?

Question

I have two types of patterns:

'nnn-nn-nnnn'

and

'nn-nnnnnnn'

where n = always a number.

how do I flag the first as 1 and second as 2? having a hard time figuring out how to do the expression. thank you.

Zohar Peled · Accepted Answer · 2017-05-23 22:22:29Z

2

Well, you can use like, but if you want to check the complete pattern (and you probably should) it's going to be messy:

 SELECT CASE 
        WHEN pattern LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]' THEN 1
        WHEN pattern LIKE '[0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN 2
        END As patternType

You could, if you are only ever going to have only these two patterns, simply use charindex:

SELECT CASE 
       WHEN charindex('-', pattern) = 4 THEN 1
       ELSE 2
       END As patternType

answered May 23, 2017 at 22:22

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to check for a string pattern in SQL Server?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related