Substring from end of the string?

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?

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))

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

<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

<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