Search code examples
sqlregexsql-server-2008patindexcharindex

Need help to finding pattern

I need to find the pattern like following ...

I have one column named Solution, In that user typed information will be captured. Some where in that column user entering his id. Id will be like ,

USER123456 USER456123

Pattern : ID always starts with 'USER' , and next 6 digits will be numeric

So ID can be any where in the string. Based on the pattern I need to extract the id.

I want achieve like ,

%USER[0-9][0-9][0-9][0-9][0-9][0-9]%

Example Text :

USERID: USER525511 I will be Expiring on 26-Jun-2014

From the above I want to extract

USER525511

Thanks in advance

Solution

  • Is this what you want?

    select substring(col,
                 patindex('%[a-z][a-z][a-z][a-z][0-9][0-9][0-9][0-9][0-9][0-9]%', col),
                 10)