SQL Server 2005:charindex starting from the end

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.

Answer 2

This will also work:

DECLARE
     @test     VARCHAR(100)

SET @test = 'some.file.name'

SELECT
     LEFT(@test, LEN(@test) - CHARINDEX('.', REVERSE(@test)))

Answer 3

Take one ')'

declare @test varchar(100) set @test = 'some.file.name' select left(@test,charindex('.',@test)+charindex('.',@test)-1)