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

oracle sql find last occurrence of character in string
sql string contains substring
sql substring
sql substring after last occurrence of character
sql find character in string
sql last occurrence
sql server last index of
sql find last number in string

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.

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

Is there a LastIndexOf in SQL Server?, If you want everything after the last _ , then use: Wrote 2 functions, 1 to return LastIndexOf for the selected character. SUBSTRING(@source, 0, @lastindex + 1) -- +1 because index starts at 0, but length at 1, so to get 1=1 BEGIN -- Find the next occurrence SET @pos = CHARINDEX(@substr, @str, @pos); -- Nothing​  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.

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

Searching a string for the last occurrence of a given , This can be used in conjunction with the RIGHT function to extract the part of a Here CHARINDEX is searching the reversed string to find the position of the  in ms sql - t-sql Working in the domain name business, I frequently have to find the last occurrence of a sub-string in T-SQL in order to properly parse out the domain extension. For example, if I have the domain name ‘subdomain1.subdomain2.bc.ca’ and I want to know whether the domain is a .com or .ca or other domain extension, the fastest

The simplest way is....

REVERSE(SUBSTRING(REVERSE([field]),0,CHARINDEX('[expr]',REVERSE([field]))))

Find last occurence of a character within a string – SQLServerCentral, SUBSTRING(FieldA ,@pos + 1, LEN(FieldA) - (@pos + 1)) END AS FileUploaded You can use the Reverse Function to find the last occurrance. Quickest way is to reverse the string and look for the first occurrence. SQL In The Wild: Discussions on DB performance with occasional diversions into  A lot of people that use T-SQL also have a programming background. So, if you ask them for a string method that can be used to find the last instance of a character in a string, they will respond with the LastIndexOf string method.

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

CHARINDEX (Transact-SQL), Transact-SQL reference for the CHARINDEX function. expression inside a second character expression, returning the starting position of the first expression if found. A character expression containing the sequence to find. When using SC collations, both start_location and the return value count  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.

DECLARE @FilePath VARCHAR(50) = 'My\Super\Long\String\With\Long\Words'
DECLARE @FindChar VARCHAR(1) = '\'

SELECT LEN(@FilePath) - CHARINDEX(@FindChar,REVERSE(@FilePath)) AS LastOccuredAt

SQL SERVER – How to find first/last occurrence of any character , How to find first/last occurrence of any character/ word in the string is whenever we are dealing with the string manipulation in SQL Server. Using LastIndexOf and SubString in mssql. Ask Question Asked 7 years, 5 months ago. What is best way to get last indexof character in SQL 2008. 1.

SQL Server CHARINDEX() Function, Parameter, Description. substring, Required. The substring to search for. string, Required. The string to be searched. start, Optional. The position where the  Example 1: Use SUBSTRING using literal string input For example, I want to extract the word “DBA” from the input string “ Nisarg is DBA .” In an input string, the position of the character D is eleven, and we want to extract three characters, therefore, the value of the “ starting_position ” parameter is 11, and the value of the

SQL Server RIGHT() Function, Well organized and easy to understand Web building tutorials with lots of examples of how VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW Extract 3 characters from a string (starting from right): Works in: SQL Server (starting with 2008), Azure SQL Database, Azure SQL Data  Using SUBSTRING with a character string The following example shows how to return only a part of a character string. From the sys.databases table, this query returns the system database names in the first column, the first letter of the database in the second column, and the third and fourth characters in the final column.

JavaScript String lastIndexOf() Method, Well organized and easy to understand Web building tutorials with lots of examples of how to use HTML, CSS, JavaScript, SQL, PHP, Python, Bootstrap, Java and XML. The lastIndexOf() method returns the position of the last occurrence of a string is searched from the end to the beginning, but returns the index starting  Find last occurence of a character within a string – Learn more on the SQLServerCentral forums. You can use the Reverse Function to find the last occurrance.

Comments
  • Yeah, its pretty awkward. This seems like it ought to be simple, only it isn't!
  • ...this is why SQL 2005 has varchar(max) to allow normal functions
  • Ah! so "varchar(max)" is a SQL 2005 thing, which explains why it didn't work when I tried it on SQL 2000.
  • DATALENGTH fails to produce the correct result for me, though LENGTH works.
  • @Tequila and others: DATALENGTH returns the number of bytes not characters. Therefore, DATALENGTH returns 2 x the number of characters in a string for NVARCHAR strings. LEN, however, returns the number of characters, minus any trailing whitespace. I never use DATALENGTH for character length calculation unless trailing whitespace is significant and I know for sure that my datatypes are consistent, whether they be VARCHAR or NVARCHAR
  • Sorry -- I'm pretty sure I never did back when I was working with 2000, and I currently do not have access to any SQL 2000 installations.
  • Brilliant! Never would have thought to attack this problem this way!
  • Nice one! I modified for my own needs: email.Substring(0, email.lastIndexOf('@')) == SELECT LEFT(email, LEN(email)-CHARINDEX('@', REVERSE(email)))
  • Clever stuff like this is why programming is so fun!
  • why not just use right instead of left on the original instead of an extra reverse