I re-read your question again - you want letter following by any number of numbers, not just one as my pattern.

This is unfortunately hard to do (if not impossible) with T-SQL.


>>>Hi All,
>>>I am trying to create a pattern that will recognize any string, which starts with one or more alpha characters followed by one or more numbers, followed by '-' (dash), followed by one or more numbers only, e.g 'abc1-10' should return >0, but 'abc1-ab10' or 'abc1-10abc' should return 0
>>>I have tried and tried an failed. Here is my last attempt.
>>>

>>>      DECLARE @Tstr  TABLE (strvalue varchar(max),Seq int Identity)
>>>      INSERT INTO @Tstr (strvalue) VALUES 
>>>               ('a1-10'),('d5-7'),('abd9-15'),('b-10'),
>>>               ('cdf-jhsss'),('dd1-msdhe'),('egh2-xx6'),
>>>               ('egh2-egh12'),('ff4-8jj'),('asd122'),
>>>               ('U 50'),('For U9'),('1-12')
>>>      -- I have tried the following
>>>      -- I need to recognize item 1,2 an 3 only, but this code will also return 1 for the items number 7,8 an 9
>>>       select *, PATINDEX('%[a-zA-Z]%[0-9]%-%[0-9]%',strvalue) from @Tstr 
>>>
>>>
>>>

>>>Please help
>>>Thank you
>>>Yelena
>>
>>T-SQL PATINDEX is limited, so I can adjust the above a bit
>>

>>DECLARE @Tstr  TABLE (strvalue varchar(max),Seq int Identity)
>>      INSERT INTO @Tstr (strvalue) VALUES 
>>               ('a1-10'),('d5-7'),('abd9-15'),('b-10'),
>>               ('cdf-jhsss'),('dd1-msdhe'),('egh2-xx6'),
>>               ('egh2-egh12'),('ff4-8jj'),('asd122'),
>>               ('U 50'),('For U9'),('1-12')
>>      -- I have tried the following
>>      -- I need to recognize item 1,2 an 3 only, but this code will also return 1 for the items number 7,8 an 9
>>       select *, PATINDEX('%[a-zA-Z][0-9][-][0-9]%',strvalue) from @Tstr

>>
>>But I don't see a way to express numbers only after dash. We may try using [-][^a-z] but this will exclude letters but doesn't guarantee numbers only.
>>You may want to use CLR RegEx instead. If so, you may want to check this article
>>http://social.technet.microsoft.com/wiki/contents/articles/17335.regex-class.aspx
>
>Thank you very much, your changes are working better for me. Can you please explain the changes you have made. I seems misunderstood the use of the '%'. I thought that %[ ]% means any number of what ever entered in the range. Do you think if I split the value into "before" and "after" dash, I can get a better result?
>
>Thanks again for your help.