Last Index of a varchar

Posted on 2004-11-26
Medium Priority
Last Modified: 2012-05-05
Hey Everyone,

I believe this is simple but it is Friday and I just can't think for whatever reason :)  I have varchar values which will be in this type of format "EQUIP_ATTRIBUTES_V48" and what i want to do is only grab the part of the string after the last "_".  Can anyone help me in doing that?
Question by:rickydoyle
  • 2
LVL 26

Expert Comment

ID: 12681999
you can use the reverse function

declare @a varchar(200)

select right(@a, charindex('_', reverse(@a))-1)
LVL 13

Expert Comment

ID: 12682025
declare @myvar varchar(50) ,
      @String varchar(100)
set @myvar = 'EQUIP_ATTRIBUTES_V48'
SET @sTRING =  (Substring( @myvar,Charindex('_', @myvar) + 1 , len( @myvar) ))
SELECT Substring( @sTRING,Charindex('_', @sTRING) + 1 , len( @sTRING) )

or in a single statment
SELECT Substring((Substring( @myvar,Charindex('_', @myvar) + 1 , len( @myvar) )),Charindex('_', (Substring( @myvar,Charindex('_', @myvar) + 1 , len( @myvar) ))) + 1 , len( (Substring( @myvar,Charindex('_', @myvar) + 1 , len( @myvar) ))) )
LVL 26

Accepted Solution

Hilaire earned 500 total points
ID: 12682031
Or this more robust version that won't fire an error if no occurence of '_' is found in the string.

declare @a varchar(200)
select case when charindex('_', @a) = 0 then @a else right(@a, charindex('_', reverse(@a))-1) end

Author Comment

ID: 12682117
Thanks for the quick response everyone, I wish i could have given the points to both of you.

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question