none
Returning right of second occurrence. RRS feed

  • Question

  • Greetings. This works as expected:

    DECLARE @value varchar(50)
    SET @value = 'beforeSlash\afterSlash'
    SELECT @value
          ,SUBSTRING(@value, CHARINDEX('\', @value) + 1, LEN(@value))
    

    So it returns "afterSlash". But what is there was another slash:

    DECLARE @value varchar(50)
    SET @value = 'beforeSlash\afterFirstSlash\afterSecondSlash'
    SELECT @value
          ,SUBSTRING(@value, CHARINDEX('\', @value) + 1, LEN(@value))
    

    All I want returned is "afterSecondSlash", but I get too much. What if there are several slashes, and I only want what's right of the very last one?

    Thanks!


    Thanks in advance! ChrisRDBA

    Tuesday, November 19, 2019 5:08 PM

Answers

  • DECLARE @value varchar(50)
    SET @value = 'beforeSlash\afterFirstSlash\afterSecondSlash'
    SELECT @value, REVERSE(LEFT(REVERSE(@value), CHARINDEX('\', REVERSE(@value)) - 1))


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by ChrisRDBA Tuesday, November 19, 2019 5:34 PM
    Tuesday, November 19, 2019 5:17 PM

All replies

  • DECLARE @value varchar(50)
    SET @value = 'beforeSlash\afterFirstSlash\afterSecondSlash'
    SELECT @value, REVERSE(LEFT(REVERSE(@value), CHARINDEX('\', REVERSE(@value)) - 1))


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by ChrisRDBA Tuesday, November 19, 2019 5:34 PM
    Tuesday, November 19, 2019 5:17 PM
  • I appreciate it, but what if you don't know how many slashes there will be?

    	  DECLARE @value varchar(50)
    SET @value = 'beforeSlash\afterFirstSlash\afterSecondSlash\afterThirdSlash'
    SELECT @value, REVERSE(LEFT(REVERSE(@value), CHARINDEX('\', REVERSE(@value)) - 1))
    


    Thanks in advance! ChrisRDBA

    Tuesday, November 19, 2019 5:23 PM
  • You need to change the length of varchar since your sample string has 60 characters.

    DECLARE @value varchar(100)
    SET @value = 'beforeSlash\afterFirstSlash\afterSecondSlash\afterThirdSlash'
    SELECT @value, REVERSE(LEFT(REVERSE(@value), CHARINDEX('\', REVERSE(@value)) - 1))


    A Fan of SSIS, SSRS and SSAS

    Tuesday, November 19, 2019 5:25 PM
  • You need to change the length of varchar since your sample string has 60 characters.

    DECLARE @value varchar(100)
    SET @value = 'beforeSlash\afterFirstSlash\afterSecondSlash\afterThirdSlash'
    SELECT @value, REVERSE(LEFT(REVERSE(@value), CHARINDEX('\', REVERSE(@value)) - 1))


    A Fan of SSIS, SSRS and SSAS

    Doh, that was silly! Thanks!

    Thanks in advance! ChrisRDBA

    Tuesday, November 19, 2019 5:33 PM
  • Use stuff function to avoid the no slash error with substring function.

     DECLARE @value varchar(100)
    SET @value = 'beforeSlash\afterFirstSlash\afterSecondSlash'
    --SET @value = 'noSlash'
    SELECT @value,  
    Reverse(Stuff(REVERSE(@value), ISNULL(Nullif(CHARINDEX('\', REVERSE(@value)),0),len(@value)),Case when CHARINDEX('\', REVERSE(@value))=0 then 0 else len(@value) end,'') )
     
    

    Tuesday, November 19, 2019 6:55 PM
    Moderator