SQL Server Performance

Substring from end of the string?

Discussion in 'General Developer Questions' started by smartrider, Oct 23, 2006.

  1. smartrider New Member

    I am trying to do the following :

    For example : I have a String "This is Jack"

    I want to do a while loop where it will check for the ' ' (space) from the end of the string and go backwards.When it finds it then I will substring from there to the end of the string. Which in the example will be "Jack"

    I am stuck in finding a way to start looking the right side of the string instead of left.
    I already tried
    @counter = -1;
    SUBSTRING(string,Len(string),@counter);

    set @counter = @counter -1;

    Any suggestions?

  2. Adriaan New Member

    You can use REVERSE to reverse the string. Then you can call CHARINDEX on the REVERSE(string) to find the position, counting from the end of the string.

    In the end, you do a SUBSTRING based on the CHARINDEX value for the REVERSE'd string, which you subtract from the length of the string, plus the data length of search string + 1.

    DECLARE @test VARCHAR(20), @searchstring VARCHAR(10)
    SET @test = 'This is Jack'
    SET @searchstring = ' '

    SELECT SUBSTRING(@test, LEN(@test) - CHARINDEX(@searchstring, REVERSE(@test)) + DATALENGTH(@searchstring) + 1, len(@test))
  3. FrankKalis Moderator

    Wouldn't this do the same?


    SELECT REVERSE(LEFT(REVERSE(@test), CHARINDEX(@searchstring,@test)-1))


    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  4. FrankKalis Moderator

    Too much REVERSEing in here anyway....


    SELECT RIGHT((@test), CHARINDEX(@searchstring,@test)-1)


    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  5. DilliGrg Member

    quote:Originally posted by FrankKalis

    Too much REVERSEing in here anyway....


    SELECT RIGHT((@test), CHARINDEX(@searchstring,@test)-1)


    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de


    Clear AND Efficient.

    Thanks,
    DilliGrg
  6. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Too much REVERSEing in here anyway....<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT RIGHT((@test), CHARINDEX(@searchstring,@test)-1)<br /></font id="code"></pre id="code"><br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />I always use this approach when I need last word of a string [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  7. FrankKalis Moderator

    Bummer!

    Sorry for all the confusion, but both statements of mine WILL fail, when the first word hasn't the same length as the last, which is merely coincidental is this case here. This, however, SHOULD do:


    DECLARE @test VARCHAR(20), @searchstring VARCHAR(10)
    SET @test = 'is is Jack'
    SET @searchstring = ' '

    SELECT RIGHT(@test, CHARINDEX(@searchstring, REVERSE(@test))-1)

    --------------------
    Jack

    (1 row(s) affected)


    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  8. DilliGrg Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Bummer!<br /><br />Sorry for all the confusion, but both statements of mine WILL fail, when the first word hasn't the same length as the last, which is merely coincidental is this case here. This, however, SHOULD do:<br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @test VARCHAR(20), @searchstring VARCHAR(10)<br />SET @test = 'is is Jack'<br />SET @searchstring = ' '<br /><br />SELECT RIGHT(@test, CHARINDEX(@searchstring, REVERSE(@test))-1)<br /> <br />-------------------- <br />Jack<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br />Now, I saw your point Frank. It was very smart of you to revisit this again and figured out before anyone complained. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @test VARCHAR(20), @searchstring VARCHAR(10)<br />SET @test = 'It is Jack'<br />SET @searchstring = ' '<br />SELECT RIGHT((@test), CHARINDEX(@searchstring,@test)-1)<br /><br /><br />--------------------<br />ck<br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br /><br />Thanks,<br />DilliGrg
  9. FrankKalis Moderator

    Hey, nobody is allknowing and we all make mistakes from time to time. Nothing wrong with standing by ones own mistakes and correct them oneself or get corrected by others. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  10. DilliGrg Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Hey, nobody is allknowing and we all make mistakes from time to time. Nothing wrong with standing by ones own mistakes and correct them oneself or get corrected by others. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Thanks,<br />DilliGrg

Share This Page