Find index of last occurrence of a sub-string using T-SQL

Question

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

User · Accepted Answer

You are limited to small list of functions for text data type.

All I can suggest is start with PATINDEX, but work backwards from DATALENGTH-1, DATALENGTH-2, DATALENGTH-3 etc until you get a result or end up at zero (DATALENGTH-DATALENGTH)

This really is something that SQL Server 2000 simply can't handle.

Edit for other answers : REVERSE is not on the list of functions that can be used with text data in SQL Server 2000

User · Answer

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

User · Answer

I realize this is a several years old question  but     On Access 2010  you can use InStrRev   to do this  Hope this helps

User · Answer

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

User · Answer

The simplest way is      REVERSE SUBSTRING REVERSE  field   0 CHARINDEX   expr   REVERSE  field

User · Answer

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  gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

handles lookinng for something  gt  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   gt  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

User · Answer

DECLARE  FilePath VARCHAR 50     My Super Long String With Long Words  DECLARE  FindChar VARCHAR 1         SELECT LEN  FilePath  - CHARINDEX  FindChar REVERSE  FilePath   AS LastOccuredAt

User · Answer

This worked very well for me   REVERSE SUBSTRING REVERSE  field    CHARINDEX REVERSE   expr     REVERSE  field      DATALENGTH   expr     DATALENGTH  field

User · Answer

REVERSE SUBSTRING REVERSE ap description  CHARINDEX     REVERSE ap description   len ap description        worked better for me

User · Answer

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    Return the last index of a search character in a given string  Return the last index of a search sub-string  more than just a single  character  in a given string  If the search character or sub-string is not in the given string return 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    Initializes  ReturnVal to 0 indicating that  FindString is not in  String Checks the index of the  FindString in  String by using CHARINDEX    If the index of  FindString in  String is 0   ReturnVal is left as 0 If the index of  FindString in  String is  gt  0   FindString is in  String so  it calculates the last index of  FindString in  String by using REVERSE   Returns  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   gt  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

User · Answer

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

User · Answer

Reverse both your string and your substring  then search for the first occurrence

User · Answer

indexOf    lt whatever characters you are searching for in your string gt    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

User · Answer

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  lt   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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] Find index of last occurrence of a sub-string using T-SQL

Examples related to sql-server

Examples related to string

Examples related to tsql

Examples related to search

Examples related to sql-server-2000