Trim numbers from end of SQL variable

I have a SQL variable called @FileName which I need to trim numbers off the end. Heres an example of how my strings look a开发者_如何学Pythont the moment, and how i need them to be:

lorem-1 to lorem

lorem-ipsum-456 to lorem-ipsum (note: '-' in the middle remains)

foo-123-bar-1234 to foo-123-bar (note: number in the middle remains)

123-lorem to 123-lorem (note: no change as no number and '-' at the end)

Is this possible?

Thanks.

It is possible using PATINDEX and REVERSE. Try this:

SELECT LEFT(@FileName,LEN(@FileName)-patindex('%[^0-9]%',REVERSE(@FileName))+1)

Yes it's possible. Hava a look at the SUBSTRING & ASCII TSQL functions.