Last Index of a varchar

Posted on 2004-11-26
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
    LVL 26

    Expert Comment

    you can use the reverse function

    declare @a varchar(200)
    set @a = 'EQUIP_ATTRIBUTES_V48'

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

    Expert Comment

    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

    Or this more robust version that won't fire an error if no occurence of '_' is found in the string.

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

    Author Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now