SQL Server 2005:charindex starting from the end

Question

I have a string 'some.file.name',I want to grab 'some.file'.

To do that,I need to find the last occurrence of '.' in a string.

My solution is :

 declare @someStr varchar(20)

 declare @reversedStr varchar(20)

 declare @index int

 set @someStr = '001.002.003'

 set @reversedStr = reverse(@someStr)

 set @index = len(@someStr) - charindex('.',@reversedStr)

 select left(@someStr,@index)

Well,isn't it too complicated?I was just intented to using 'some.file' in a where-clause.

Anyone has a good idea?

Answer 1

What do you need to do with it?? Do you need to grab the characters after the last occurence of a given delimiter?

If so: reverse the string and search using the normal CHARINDEX:

declare @test varchar(100)
set @test = 'some.file.name'

declare @reversed varchar(100)
set @reversed = REVERSE(@test)

select 
    REVERSE(SUBSTRING(@reversed, CHARINDEX('.', @reversed)+1, 100))

You'll get back "some.file" - the characters up to the last "." in the original file name.

There's no "LASTCHARINDEX" or anything like that in SQL Server directly. What you might consider doing in SQL Server 2005 and up is great a .NET extension library and deploy it as an assembly into SQL Server - T-SQL is not very strong with string manipulation, whereas .NET really is.