SQL CHARINDEX Last Occurrence of a Word or Char | Tutorial

SQL CHARINDEX last occurrence of a word or char is an easy task with this tutorial by our experts. 

At Bobcares, we offer solutions for every query, big and small, as a part of our SQL Server Support Services.

Let’s take a look at how our SQL Server Support Team helped our customers with learning more about using CHARINDEX to find the last occurrence of a word or char.

How to find SQL CHARINDEX last occurrence of a Word or Char

The CHARINDEX() function returns the position of a substring in a string. The syntax of the function is:

CHARINDEX(substring, string, start)

However, if the function is not able to locate the substring in the string, it returns 0.

Interestingly, this function comes in handy to find a particular char or word in a string. Moreover, we can use the function to pinpoint the last occurrence of a char or substring in an SQL query.

For instance:

DECLARE @String AS VARCHAR(100)
DECLARE @Sub_String AS VARCHAR(100)
SET @String ='Bobcares offers professional outsourced support and server management services.'
SET @Sub_String='and'
--Finds the last occurrence of and in the string
SELECT DATALENGTH(@String)-CHARINDEX(REVERSE(@Sub_String)
,REVERSE(@String))-1 As [Last occurrence]

This leads to 49 as the output.

While SQL CHARINDEX() helps us find the first occurrence of a substring, we can reverse the string to find the last appearance of the substring as seen in the example above.

Furthermore, this would come in handy to extract just the filename from a filepath. For instance, if the file path is:

C:\Program Files\Microsoft SQL Server\MSSQL\DATA\EmployeeDetails_Data.mdf

We can use the SQL CHARINDEX() last occurrence option to detect the last appearance of \. For example:

SELECT RIGHT(@fullfilepath , CHARINDEX ('\' ,REVERSE(@fullfilepath))-1)

Let us know in the comments how your attempt at using the CHARINDEX last occurrence option works out.

[Need assistance with a different issue? We are available 24/7.]

Conclusion

In a nutshell, our skilled SQL Server Support Engineers at Bobcares demonstrated how to use CHARINDEX() to find the last occurrence of a word or char.