>>>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')
>>>
>>>
>>> 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')
>>
>>
>> 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.
% means any character. Since you want this pattern somethingLetterNumber-Number...
this can be expressed as
% [a-z][0-9][-][0-9]
We need to put - (dash) into [] because this character has special meaning and need to be escaped.
So, our pattern will be %[a-z][0-9][-][0-9]%
This pattern will be translated as anything following by letter then by number then by dash then number then anything.
It is not easy to make sure that the rest of the string is numbers only since we don't know how many numbers are there.
If you need this pattern as LIKE expression or in the where clause, we may split and use pattern based on the length.
You may also ask this question here
http://social.msdn.microsoft.com/Forums/en-US/transactsql/threadsMay be there is smarter solution here I don't see.
If it's not broken, fix it until it is.
My Blog