Parse a string with SQL

[sdtucker]

How do I parse a string with SQL???? For example I have a string stored in a column:

"12345/This a and That/999999/WR493-22"

How can I quickly extract the 999999 from the string?

Any suggestions would be a tremendous help as I am STUCK!

 

[bev]

A little more information would help. On what basis do you know that it is the "999999" that you want? Is it because it's before the last "/" and after the previous one? Or is it on some other basis, like that it's the longest numeric string?
Bev

 

[wengyan]

DECLARE @Source varchar(60)
DECLARE @Len int
DECLARE @Position int
DECLARE @pattern varchar(10)
DECLARE @Result varchar(10)

SELECT @Source='12345/This a and That/999999/WR493-22'
SELECT @pattern='999999'
SELECT @Position=CHARINDEX(@pattern, @source,0)

SELECT @Result=substring(@source,@Position,len(@pattern))

SELECT @Source AS Source,@Position AS Position,@Result AS Result



Source Position Result
------------------------------------------------------------ ----------- ----------
12345/This a and That/999999/WR493-22 23 999999

 

[sdtucker]

Basically I have an entire recordset where the value I need is the third value(1/2/3/4). It's always the third value, I've been trying something similar to wengyan (Thanks), but cannot seem to get it.

DECLARE @Var as varchar(50)
DECLARE @Var2 as varchar(50)
SET @Var = '2541/6113625/W284901.02/01001/J/100/309350.'
SET @Var2 = right(@Var, len(@Var)-CHARINDEX('/', @Var))
SELECT Left(@Var2, charindex('/', @Var2)-1)

[I was testing on the second string]

I need to select two colums, one of which contains the nested string that I need, the other contains a dollar amount.

Select Description, [Parsed String], Dollars From Table1

Thanks for the replies. Any more suggestions?