SQL Server 2005:charindex starting from the end
Solution 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.
Solution 2
A very simple way is:
SELECT
RIGHT(@str, CHARINDEX('.', REVERSE(@str)) - 1)
Solution 3
This will also work:
DECLARE
@test VARCHAR(100)
SET @test = 'some.file.name'
SELECT
LEFT(@test, LEN(@test) - CHARINDEX('.', REVERSE(@test)))
Solution 4
Take one ')'
declare @test varchar(100)
set @test = 'some.file.name'
select left(@test,charindex('.',@test)+charindex('.',@test)-1)
Solution 5
CREATE FUNCTION [dbo].[Instr] (
-------------------------------------------------------------------------------------------------
-- Name: [dbo].[Instr]
-- Purpose: Find The Nth Value Within A String
-------------------------------------------------------------------------------------------------
-- Revisions:
-- 25-FEB-2011 - HESSR - Initial Revision
-------------------------------------------------------------------------------------------------
-- Parameters:
-- 1) @in_FindString - NVARCHAR(MAX) - INPUT - Input Find String
-- 2) @in_String - NVARCHAR(MAX) - INPUT - Input String
-- 3) @in_StartPos - SMALLINT - INPUT - Position In The String To Start Looking From
-- (If Start Position Is Negative, Search Begins At The End Of The String)
-- (Negative 1 Starts At End Position 1, Negative 3 Starts At End Position Minus 2)
-- 4) @in_Nth - SMALLINT - INPUT - Nth Occurrence To Find The Location For
-------------------------------------------------------------------------------------------------
-- Returns: SMALLINT - Position Of String Segment (Not Found = 0)
-------------------------------------------------------------------------------------------------
@in_FindString NVARCHAR(MAX),
@in_String NVARCHAR(MAX),
@in_StartPos SMALLINT = NULL,
@in_Nth SMALLINT = NULL
)
RETURNS SMALLINT
AS
BEGIN
DECLARE @loc_FindString NVARCHAR(MAX);
DECLARE @loc_String NVARCHAR(MAX);
DECLARE @loc_Position SMALLINT;
DECLARE @loc_StartPos SMALLINT;
DECLARE @loc_Nth SMALLINT;
DECLARE @loc_Idx SMALLINT;
DECLARE @loc_FindLength SMALLINT;
DECLARE @loc_Length SMALLINT;
SET @loc_FindString = @in_FindString;
SET @loc_String = @in_String;
SET @loc_Nth = ISNULL(ABS(@in_Nth), 1);
SET @loc_FindLength = LEN(@loc_FindString+N'.') - 1;
SET @loc_Length = LEN(@loc_String+N'.') - 1;
SET @loc_StartPos = ISNULL(@in_StartPos, 1);
SET @loc_Idx = 0;
IF (@loc_StartPos = ABS(@loc_StartPos))
BEGIN
WHILE (@loc_Idx < @loc_Nth)
BEGIN
SET @loc_Position = CHARINDEX(@loc_FindString,@loc_String,@loc_StartPos);
IF (@loc_Position > 0)
SET @loc_StartPos = @loc_Position + @loc_FindLength
ELSE
SET @loc_Idx = @loc_Nth;
SET @loc_Idx = @loc_Idx + 1;
END;
END
ELSE
BEGIN
SET @loc_StartPos = ABS(@loc_StartPos);
SET @loc_FindString = REVERSE(@in_FindString);
SET @loc_String = REVERSE(@in_String);
WHILE (@loc_Idx < @loc_Nth)
BEGIN
SET @loc_Position = CHARINDEX(@loc_FindString,@loc_String,@loc_StartPos);
IF (@loc_Position > 0)
SET @loc_StartPos = @loc_Position + @loc_FindLength
ELSE
SET @loc_Idx = @loc_Nth;
SET @loc_Idx = @loc_Idx + 1;
END;
IF (@loc_Position > 0)
SET @loc_Position = @loc_Length - @loc_Position + (1 - @loc_FindLength) + 1;
END;
RETURN (@loc_Position);
END;
GO
Shuo
Updated on May 07, 2020Comments
-
Shuo almost 4 years
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?
-
Shuo over 14 yearsHey Marc, Thanks for your reply. I have a string like 'some.file.name' I want to grab 'some.file'.if using LEFT,I need to know the index of the last delimiter.Do you have any good idea to grab 'some.file'?
-
John Sansom over 14 years@Shuo: Why not have a go yourself and post your best effort. Then others may be more inclined to assist you.
-
marc_s over 14 years@Shuo: update my answer for your scenario - should work just fine.
-
Shuo over 14 years@John Sansom:Thanks John!I have revised my question.
-
Tom H over 14 yearsHeh! After looking through Marc's code and reading all the comments, then getting distracted by something else I totally forgot what your solution had been :)
-
David over 6 yearsit's 2017 outside, shouldn't T-SQL have a built in function for CHARINDEX LAST ?
-
Nick Rolando about 6 yearsThis actually grabs the extension, not the name of the file.