T-SQL Pattern matching issue

I need to determine whether a given string is of the format 'abcd efg 开发者_Go百科-4' i.e '% -number'. I need to isolate the '4', and increment it to '5'.

The rest of the string can contain dates and times like so:

abcd efg - ghis asdjh - 07-07-2011 05-30-34 AM

this string, for instance, does NOT satisfy the pattern i.e. -[number]. For this string, the output from my SQL should be

abcd efg - ghis asdjh - 07-07-2011 05-30-34 AM -1

If the above is input, I should get:

abcd efg - ghis asdjh - 07-07-2011 05-30-34 AM -2

The number can be any number of digits i.e. so a string could be 'abcd efg -123', and my T-SQL would return 'abcd efg -124'

This T-SQL code is going to be embedded in a stored procedure. I know I could implement a .Net stored proc/function and use Regex to do this, however there are various access issues which I have to get around in order to switch-on the CLR on the SQL Server.

I have tried the following patterns:

• '%[ ][-]%[0-9]', this works for most cases, but put in an extra space somewhere and it fails
• '%[ ][-]%[^a-z][^A-Z]%[0-9]', this manages to skip '-4' (as shown in the above example), but works in several cases, such
• '%[ ][-][^a-z][^A-Z]%[0-9]', this again works in some, doesn't in others...

This pattern ' -[number]' would always be at the end of the string, if it's not present the code would append it, as seen in the examples above.

I would like a pattern that works for ALL cases...

---

Interesting problem. You do realize that this is much more difficult than it really needs to be. If you properly normalized your table so that each column only contains one piece of information, you wouldn't have a problem at all. If it's possible, I would strongly encourage you to consider normalizing this data.

If you cannot normalize the data, then I would approach this backwards. You said the dash-number you are looking for would always appear at the end of the data. Why not reverse the string, parse it, and put it back together. By reversing the string, you will be looking for '[0-9]%[-]' which is a whole lot easier to find.

I put your test data in to a table variable so that I could test the code I've come up with. You can copy/paste this to a query window to see how it works.

Declare @Temp Table(Data VarChar(100))

Insert Into @Temp Values('abcd efg - ghis asdjh - 07-07-2011 05-30-34 AM')
Insert Into @Temp Values('abcd efg - ghis asdjh - 07-07-2011 05-30-34 AM -1')
Insert Into @Temp Values('abcd efg - ghis asdjh - 07-07-2011 05-30-34 AM -2')
Insert Into @Temp Values('abcd efg -123')

Select Case When PatIndex('[0-9]%[-]%', Reverse(Data)) = 1
            Then Left(Data, Len(Data)-CharIndex('-', Reverse(Data))) + '-' +
                 Convert(VarChar(20), 1+Convert(Int, Reverse(Left(Reverse(Data), CharIndex('-', Reverse(Data))-1))))
            Else Data + ' -1'
            End
From   @Temp