Bobcares

SQL CHARINDEX Last Occurrence of a Word or Char | Tutorial

PDF Header PDF Footer

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.

 

SQL CHARINDEX Last Occurrence

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.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Get featured on the Bobcares blog and share your expertise with a global tech audience.

WRITE FOR US
server management

Spend time on your business, not on your servers.

TALK TO US

Or click here to learn more.

Speed issues driving customers away?
We’ve got your back!