CREATE FUNCTION ParseValues  
(@String varchar(8000),
@Delimiter char(1)  
)  
RETURNS @RESULTS TABLE  
(ID int identity(1,1),  
Val int varchar(1000)  )  
AS  
BEGIN  
DECLARE @Value varchar(100)  
  
WHILE @String is not null  
BEGIN  
SELECT @Value=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN LEFT(@String,CHARINDEX(@Delimiter,@String)-1) ELSE @String END,  
@String=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN SUBSTRING(@String,CHARINDEX(@Delimiter,@String)+1,LEN(@String)) ELSE NULL END  
INSERT INTO @RESULTS (Val)   
SELECT @Value   
END  
RETURN  
END

DECLARE @String varchar(1000)
SET @String='RITA_RICE_051208_RRICE08_NEWYORK'
SELECT TOP 1 Val FROM
(
SELECT TOP 2 Val,ID FROM dbo.ParseValues(@String,'_') ORDER BY ID DESC
)t
ORDER BY t.ID ASC

quote:

---

Originally posted by madhivanan

Val int

should be

Val varchar(100)

then

DECLARE @String varchar(1000)
SET @String='RITA_RICE_051208_RRICE08_NEWYORK'
SELECT TOP 1 Val FROM
(
SELECT TOP 2 Val,id FROM dbo.ParseValues(@String,'_') ORDER BY ID DESC
)t
ORDER BY t.ID ASC


Madhivanan

Failing to plan is Planning to fail

---