quote:

---

Originally posted by ditch

Or you could try this:

select substring(mystring, 8, ((charindex('$', mystring, 2) - 1) - 7)) as empid,
substring(mystring, charindex('$EmpMgr=', mystring) + 8, ((charindex('$', mystring, charindex('$EmpMgr=', mystring) + 8)) - (charindex('$EmpMgr=', mystring) + 8))),
right(left(mystring, len(mystring) - 2), charindex('=', (left(reverse(mystring), len(mystring) - 1)))-3) as DeptNo
from mytable


Duane.

---

quote:

---

Originally posted by Ramnadh

Thanx for your reply.

But it is better if the string consists of two parameters. I will get the string parameters between $ and $ upto 6. so i have to take all those parameters from the string. can you help me.

quote:

---

Originally posted by ditch

Or you could try this:

select substring(mystring, 8, ((charindex('$', mystring, 2) - 1) - 7)) as empid,
substring(mystring, charindex('$EmpMgr=', mystring) + 8, ((charindex('$', mystring, charindex('$EmpMgr=', mystring) + 8)) - (charindex('$EmpMgr=', mystring) + 8))),
right(left(mystring, len(mystring) - 2), charindex('=', (left(reverse(mystring), len(mystring) - 1)))-3) as DeptNo
from mytable


Duane.

---

Ramnadh

---

quote:

---

Originally posted by AndrewMurphy

Beware..."can keep them in a cursor and get the values and use them accordingly"....just be aware of the 'general poor' performance cursors deliver......

search here for examples....

---

--tally table used below
create table numbers ( n int primary key)
declare @n int; set @n=0
while @n <= 255
begin
	insert into numbers
	select @n 
	set @n = @n+1
end

declare @input varchar(255)
set @input = 'Empn=99;EmpMgr=123;DeptNo=20;Orgn=2;Inchargen=23'

select 
	left(input,charindex('=',input,1)-1) [key], 
	substring(input,charindex('=',input,1)+1,len(input)) [value] 
from
(
	select NullIf(SubString(';' + @input + ';' , n , CharIndex(';' , ';' + @input + ';' , n) - n) , '') input 
	from numbers
	where n <= Len(';' + @input + ';') AND SubString(';' + @input + ';' , n - 1, 1) = ';' 
) d