Hi,
By mistake i posted this topic in .Net section. Thats y posting again in SQL server section.
In database i have stored the membership no. of members as L/BNG/01234 or SLM/HYD/01235/2004 like this. I have to retrieve the 3rd string after slash i.e 01234 or 01235 respectively.
What will the stored procedure to get this value. I have to work with this 3rd string of all the members.

Thanks and regards
Lily

May be..

SELECT SUBSTRING(Member_NO,CharIndex('/',Member_No)+5,5) FROM Table

provide some more sample data or try manipulating SUBSTRING and CHARINDEX

Thanks it worked.

Sorry it doesnt work for all the combinations.
eg.
1. SELECT SUBSTRING('a/del/123/x',CharIndex('/','a/del/123/x')+5,5) FROM table where userid='AAS219'

Giving the result as 123/x

2. SELECT SUBSTRING('a/del/12345/x',CharIndex('/','a/del/12345/x')+5,4) FROM table where userid='AAS219'
Giving the result as 1234.

Both the combinations are giving wrong answers.

Correct one is :

create procedure getthirdvalue
as
begin
set nocount on
declare @strEmployeeid varchar(50)
declare @i int
set @i=0
set @strEmployeeID='L/wer/1213/03-04'
create TABLE #emp1 (nid int identity(1,1),EmployeeID varchar(50) not null)
declare @thirdvalue varchar(50)
IF CHARINDEX('/', @strEmployeeID) > 0
 BEGIN
     WHILE CHARINDEX('/',@strEmployeeID) > 0
    BEGIN
    INSERT #emp1(EmployeeID) VALUES(SUBSTRING(@strEmployeeID, 1, CHARINDEX('/', @strEmployeeID) - 1))
    set @strEmployeeID = STUFF(@strEmployeeID, 1, CHARINDEX('/', @strEmployeeID),'')
    END
    select @thirdvalue=EmployeeID from #emp1 where nid=3
    drop table #emp1
    print @thirdvalue
end
end



Regards
Lily