Is there a straightforward way of finding the index of the last occurrence of a string using SQL? I am using SQL Server 2000 right now. I basically need the functionality that the .NET System.String.LastIndexOf
method provides. A little googling revealed this - Function To Retrieve Last Index - but that does not work if you pass in a "text" column expression. Other solutions found elsewhere work only so long as the text you are searching for is 1 character long.
I will probably have to cook a function up. If I do so, I will post it here so you folks can look at it and maybe make use of.
This question is related to
sql-server
string
tsql
search
sql-server-2000
I needed to find the nth last position of a backslash in a folder path. Here is my solution.
/*
http://stackoverflow.com/questions/1024978/find-index-of-last-occurrence-of-a-sub-string-using-t-sql/30904809#30904809
DROP FUNCTION dbo.GetLastIndexOf
*/
CREATE FUNCTION dbo.GetLastIndexOf
(
@expressionToFind VARCHAR(MAX)
,@expressionToSearch VARCHAR(8000)
,@Occurrence INT = 1 -- Find the nth last
)
RETURNS INT
AS
BEGIN
SELECT @expressionToSearch = REVERSE(@expressionToSearch)
DECLARE @LastIndexOf INT = 0
,@IndexOfPartial INT = -1
,@OriginalLength INT = LEN(@expressionToSearch)
,@Iteration INT = 0
WHILE (1 = 1) -- Poor man's do-while
BEGIN
SELECT @IndexOfPartial = CHARINDEX(@expressionToFind, @expressionToSearch)
IF (@IndexOfPartial = 0)
BEGIN
IF (@Iteration = 0) -- Need to compensate for dropping out early
BEGIN
SELECT @LastIndexOf = @OriginalLength + 1
END
BREAK;
END
IF (@Occurrence > 0)
BEGIN
SELECT @expressionToSearch = SUBSTRING(@expressionToSearch, @IndexOfPartial + 1, LEN(@expressionToSearch) - @IndexOfPartial - 1)
END
SELECT @LastIndexOf = @LastIndexOf + @IndexOfPartial
,@Occurrence = @Occurrence - 1
,@Iteration = @Iteration + 1
IF (@Occurrence = 0) BREAK;
END
SELECT @LastIndexOf = @OriginalLength - @LastIndexOf + 1 -- Invert due to reverse
RETURN @LastIndexOf
END
GO
GRANT EXECUTE ON GetLastIndexOf TO public
GO
Here are my test cases which pass
SELECT dbo.GetLastIndexOf('f','123456789\123456789\', 1) as indexOf -- expect 0 (no instances)
SELECT dbo.GetLastIndexOf('\','123456789\123456789\', 1) as indexOf -- expect 20
SELECT dbo.GetLastIndexOf('\','123456789\123456789\', 2) as indexOf -- expect 10
SELECT dbo.GetLastIndexOf('\','1234\6789\123456789\', 3) as indexOf -- expect 5
handles lookinng for something > 1 char long. feel free to increase the parm sizes if you like.
couldnt resist posting
drop function if exists lastIndexOf
go
create function lastIndexOf(@searchFor varchar(100),@searchIn varchar(500))
returns int
as
begin
if LEN(@searchfor) > LEN(@searchin) return 0
declare @r varchar(500), @rsp varchar(100)
select @r = REVERSE(@searchin)
select @rsp = REVERSE(@searchfor)
return len(@searchin) - charindex(@rsp, @r) - len(@searchfor)+1
end
and tests
select dbo.lastIndexof('greg','greg greg asdflk; greg sadf' ) -- 18
select dbo.lastIndexof('greg','greg greg asdflk; grewg sadf' ) --5
select dbo.lastIndexof(' ','greg greg asdflk; grewg sadf' ) --24
REVERSE(SUBSTRING(REVERSE(ap_description),CHARINDEX('.',REVERSE(ap_description)),len(ap_description)))
worked better for me
If you want to get the index of the last space in a string of words, you can use this expression RIGHT(name, (CHARINDEX(' ',REVERSE(name),0)) to return the last word in the string. This is helpful if you want to parse out the last name of a full name that includes initials for the first and /or middle name.
This answer meets the requirements of the OP. specifically it allows the needle to be more than a single character and it does not generate an error when needle is not found in haystack. It seemed to me that most (all?) of the other answers did not handle those edge cases. Beyond that I added the "Starting Position" argument provided by the native MS SQL server CharIndex function. I tried to exactly mirror the specification for CharIndex except to process right to left instead of left to right. eg I return null if either needle or haystack is null and I return zero if needle is not found in haystack. One thing that I could not get around is that with the built in function the third parameter is optional. With SQL Server user defined functions, all parameters must be provided in the call unless the function is called using "EXEC" . While the third parameter must be included in the parameter list, you can provide the keyword "default" as a placeholder for it without having to give it a value (see examples below). Since it is easier to remove the third parameter from this function if not desired than it would be to add it if needed I have included it here as a starting point.
create function dbo.lastCharIndex(
@needle as varchar(max),
@haystack as varchar(max),
@offset as bigint=1
) returns bigint as begin
declare @position as bigint
if @needle is null or @haystack is null return null
set @position=charindex(reverse(@needle),reverse(@haystack),@offset)
if @position=0 return 0
return (len(@haystack)-(@position+len(@needle)-1))+1
end
go
select dbo.lastCharIndex('xyz','SQL SERVER 2000 USES ANSI SQL',default) -- returns 0
select dbo.lastCharIndex('SQL','SQL SERVER 2000 USES ANSI SQL',default) -- returns 27
select dbo.lastCharIndex('SQL','SQL SERVER 2000 USES ANSI SQL',1) -- returns 27
select dbo.lastCharIndex('SQL','SQL SERVER 2000 USES ANSI SQL',11) -- returns 1
I realize this is a several years old question, but...
On Access 2010
, you can use InStrRev()
to do this. Hope this helps.
This worked very well for me.
REVERSE(SUBSTRING(REVERSE([field]), CHARINDEX(REVERSE('[expr]'), REVERSE([field])) + DATALENGTH('[expr]'), DATALENGTH([field])))
Old but still valid question, so heres what I created based on the info provided by others here.
create function fnLastIndexOf(@text varChar(max),@char varchar(1))
returns int
as
begin
return len(@text) - charindex(@char, reverse(@text)) -1
end
Reverse both your string and your substring, then search for the first occurrence.
I came across this thread while searching for a solution to my similar problem which had the exact same requirement but was for a different kind of database that was also lacking the REVERSE
function.
In my case this was for a OpenEdge (Progress) database, which has a slightly different syntax. This made the INSTR
function available to me that most Oracle typed databases offer.
So I came up with the following code:
SELECT
INSTR(foo.filepath, '/',1, LENGTH(foo.filepath) - LENGTH( REPLACE( foo.filepath, '/', ''))) AS IndexOfLastSlash
FROM foo
However, for my specific situation (being the OpenEdge (Progress) database) this did not result into the desired behaviour because replacing the character with an empty char gave the same length as the original string. This doesn't make much sense to me but I was able to bypass the problem with the code below:
SELECT
INSTR(foo.filepath, '/',1, LENGTH( REPLACE( foo.filepath, '/', 'XX')) - LENGTH(foo.filepath)) AS IndexOfLastSlash
FROM foo
Now I understand that this code won't solve the problem for T-SQL because there is no alternative to the INSTR
function that offers the Occurence
property.
Just to be thorough I'll add the code needed to create this scalar function so it can be used the same way like I did in the above examples.
-- Drop the function if it already exists
IF OBJECT_ID('INSTR', 'FN') IS NOT NULL
DROP FUNCTION INSTR
GO
-- User-defined function to implement Oracle INSTR in SQL Server
CREATE FUNCTION INSTR (@str VARCHAR(8000), @substr VARCHAR(255), @start INT, @occurrence INT)
RETURNS INT
AS
BEGIN
DECLARE @found INT = @occurrence,
@pos INT = @start;
WHILE 1=1
BEGIN
-- Find the next occurrence
SET @pos = CHARINDEX(@substr, @str, @pos);
-- Nothing found
IF @pos IS NULL OR @pos = 0
RETURN @pos;
-- The required occurrence found
IF @found = 1
BREAK;
-- Prepare to find another one occurrence
SET @found = @found - 1;
SET @pos = @pos + 1;
END
RETURN @pos;
END
GO
To avoid the obvious, when the REVERSE
function is available you do not need to create this scalar function and you can just get the required result like this:
SELECT
LEN(foo.filepath) - CHARINDEX('/', REVERSE(foo.filepath))+1 AS LastIndexOfSlash
FROM foo
DECLARE @FilePath VARCHAR(50) = 'My\Super\Long\String\With\Long\Words'
DECLARE @FindChar VARCHAR(1) = '\'
SELECT LEN(@FilePath) - CHARINDEX(@FindChar,REVERSE(@FilePath)) AS LastOccuredAt
Hmm, I know this is an old thread, but a tally table could do this in SQL2000 (or any other database):
DECLARE @str CHAR(21),
@delim CHAR(1)
SELECT @str = 'Your-delimited-string',
@delim = '-'
SELECT
MAX(n) As 'position'
FROM
dbo._Tally
WHERE
substring(@str, _Tally.n, 1) = @delim
A tally table is just a table of incrementing numbers.
The substring(@str, _Tally.n, 1) = @delim
gets the position of each delimiter, then you just get the maximum position in that set.
Tally tables are awesome. If you haven't used them before, there is a good article on SQL Server Central (Free reg, or just use Bug Me Not (http://www.bugmenot.com/view/sqlservercentral.com)).
*EDIT: Removed n <= LEN(TEXT_FIELD)
, as you can't use LEN() on the TEXT type. As long as the substring(...) = @delim
remains though the result is still correct.
@indexOf = <whatever characters you are searching for in your string>
@LastIndexOf = LEN([MyField]) - CHARINDEX(@indexOf, REVERSE([MyField]))
Haven't tested, it might be off by one because of zero index, but works in SUBSTRING
function when chopping off from @indexOf
characters to end of your string
SUBSTRING([MyField], 0, @LastIndexOf)
The simplest way is....
REVERSE(SUBSTRING(REVERSE([field]),0,CHARINDEX('[expr]',REVERSE([field]))))
Some of the other answers return an actual string whereas I had more need to know the actual index int. And the answers that do that seem to over-complicate things. Using some of the other answers as inspiration, I did the following...
First, I created a function:
CREATE FUNCTION [dbo].[LastIndexOf] (@stringToFind varchar(max), @stringToSearch varchar(max))
RETURNS INT
AS
BEGIN
RETURN (LEN(@stringToSearch) - CHARINDEX(@stringToFind,REVERSE(@stringToSearch))) + 1
END
GO
Then, in your query you can simply do this:
declare @stringToSearch varchar(max) = 'SomeText: SomeMoreText: SomeLastText'
select dbo.LastIndexOf(':', @stringToSearch)
The above should return 23 (the last index of ':')
Hope this made it a little easier for someone!
This code works even if the substring contains more than 1 character.
DECLARE @FilePath VARCHAR(100) = 'My_sub_Super_sub_Long_sub_String_sub_With_sub_Long_sub_Words'
DECLARE @FindSubstring VARCHAR(5) = '_sub_'
-- Shows text before last substing
SELECT LEFT(@FilePath, LEN(@FilePath) - CHARINDEX(REVERSE(@FindSubstring), REVERSE(@FilePath)) - LEN(@FindSubstring) + 1) AS Before
-- Shows text after last substing
SELECT RIGHT(@FilePath, CHARINDEX(REVERSE(@FindSubstring), REVERSE(@FilePath)) -1) AS After
-- Shows the position of the last substing
SELECT LEN(@FilePath) - CHARINDEX(REVERSE(@FindSubstring), REVERSE(@FilePath)) AS LastOccuredAt
To get the part before the last occurence of the delimiter (works only for NVARCHAR
due to DATALENGTH
usage):
DECLARE @Fullstring NVARCHAR(30) = '12.345.67890.ABC';
DECLARE @Delimiter CHAR(1) = '.';
SELECT SUBSTRING(@Fullstring, 1, DATALENGTH(@Fullstring)/2 - CHARINDEX(@Delimiter, REVERSE(@Fullstring)));
This answer uses MS SQL Server 2008 (I don't have access to MS SQL Server 2000), but the way I see it according to the OP are 3 situations to take into consideration. From what I've tried no answer here covers all 3 of them:
0
The function I came up with takes 2 parameters:
@String NVARCHAR(MAX)
: The string to be searched
@FindString NVARCHAR(MAX)
: Either a single character or a sub-string to get the last
index of in @String
It returns an INT
that is either the positive index of @FindString
in @String
or 0
meaning that @FindString
is not in @String
Here's an explanation of what the function does:
@ReturnVal
to 0
indicating that @FindString
is not in @String
@FindString
in @String
by using CHARINDEX()
@FindString
in @String
is 0
, @ReturnVal
is left as 0
@FindString
in @String
is > 0
, @FindString
is in @String
so
it calculates the last index of @FindString
in @String
by using REVERSE()
@ReturnVal
which is either a positive number that is the last index of
@FindString
in @String
or 0
indicating that @FindString
is not in @String
Here's the create function script (copy and paste ready):
CREATE FUNCTION [dbo].[fn_LastIndexOf]
(@String NVARCHAR(MAX)
, @FindString NVARCHAR(MAX))
RETURNS INT
AS
BEGIN
DECLARE @ReturnVal INT = 0
IF CHARINDEX(@FindString,@String) > 0
SET @ReturnVal = (SELECT LEN(@String) -
(CHARINDEX(REVERSE(@FindString),REVERSE(@String)) +
LEN(@FindString)) + 2)
RETURN @ReturnVal
END
Here's a little bit that conveniently tests the function:
DECLARE @TestString NVARCHAR(MAX) = 'My_sub2_Super_sub_Long_sub1_String_sub_With_sub_Long_sub_Words_sub2_'
, @TestFindString NVARCHAR(MAX) = 'sub'
SELECT dbo.fn_LastIndexOf(@TestString,@TestFindString)
I have only run this on MS SQL Server 2008 because I don't have access to any other version but from what I've looked into this should be good for 2008+ at least.
Enjoy.
If you are using Sqlserver 2005 or above, using REVERSE
function many times is detrimental to performance, below code is more efficient.
DECLARE @FilePath VARCHAR(50) = 'My\Super\Long\String\With\Long\Words'
DECLARE @FindChar VARCHAR(1) = '\'
-- Shows text before last slash
SELECT LEFT(@FilePath, LEN(@FilePath) - CHARINDEX(@FindChar,REVERSE(@FilePath))) AS Before
-- Shows text after last slash
SELECT RIGHT(@FilePath, CHARINDEX(@FindChar,REVERSE(@FilePath))-1) AS After
-- Shows the position of the last slash
SELECT LEN(@FilePath) - CHARINDEX(@FindChar,REVERSE(@FilePath)) AS LastOccuredAt
I know that it will be inefficient but have you considered casting the text
field to varchar
so that you can use the solution provided by the website you found? I know that this solution would create issues as you could potentially truncate the record if the length in the text
field overflowed the length of your varchar
(not to mention it would not be very performant).
Since your data is inside a text
field (and you are using SQL Server 2000) your options are limited.
Straightforward way? No, but I've used the reverse. Literally.
In prior routines, to find the last occurence of a given string, I used the REVERSE() function, followed CHARINDEX, followed again by REVERSE to restore the original order. For instance:
SELECT
mf.name
,mf.physical_name
,reverse(left(reverse(physical_name), charindex('\', reverse(physical_name)) -1))
from sys.master_files mf
shows how to extract the actual database file names from from their "physical names", no matter how deeply nested in subfolders. This does search for only one character (the backslash), but you can build on this for longer search strings.
The only downside is, I don't know how well this will work on TEXT data types. I've been on SQL 2005 for a few years now, and am no longer conversant with working with TEXT -- but I seem to recall you could use LEFT and RIGHT on it?
Philip
Source: Stackoverflow.com