Selecting variable character length from end of a string 3

[reidtw]

Hi, using both SQL Srvr 2K DBs & OS and SQL 2005 DBs/OS.

Using t-sql, I need to extract the end of a string where the part I need is of a variable length: I cannot obviously use RIGHT(<string>,<int>), as this needs to return a specific number of characters it's nae use. I've fallen off my board many times surfing for an answer!

Examples of the string would be: -

"He had 20 eggs and he fried 12"
"He had 200 eggs and he fried 120"

where there is always a space before the number I need to extract from the end: note that there is a number inside the string and I do not want to extract this, so the code I know of that can extract all numeric digits is also no use.

Any suggestions much appreciated.

T Reid

 

[bborissov]

DECLARE @test TABLE (Fld1 varchar(200))
INSERT INTO @Test VALUES ('He had 20 eggs and he fried 12')
INSERT INTO @Test VALUES ('He had 200 eggs and he fried 120')

SELECT REVERSE(LEFT(REVERSE(Fld1),CHARINDEX(' ',REVERSE(Fld1))-1)) FROM @Test

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[ESquared]

no need to reverse(left(reverse())), just use right()

DECLARE @test TABLE (Fld1 varchar(200))
INSERT INTO @Test VALUES ('He had 20 eggs and he fried 12')
INSERT INTO @Test VALUES ('He had 200 eggs and he fried 120')

SELECT Right(Fld1, CharIndex(' ', Reverse(Fld1) + ' ') -1)) FROM @Test

note the + ' ' to make sure at least something is returned.

 

[reidtw]

Thank you both for equally able solutions, though Esquared's has worked better in SQL2005.

One slight typo in it: select statement should read: -

SELECT Right(Fld1, CharIndex(' ', Reverse(Fld1) + ' ') -1)

Thanks again

 

[ESquared]

yeah, extra parenthesis... done on the fly

I just stole Borisov's code so he gets the credit.