quote:

---

Originally posted by pinkfloyd43

I have a column ActivityDescription which contains the literl value below:


From: Account: DEMO Dictator: Doctor, Demo To: Account: EMC Dictator: Clark, Nellda



I have been trying to extract the data bolded for too long. Anybody have a easy solution for this, SubString() and PatIndex() have been tried but can't seem to get it exactly correct!

---

SELECT SUBSTRING (Col,PATINDEX('%From: Account:%',Col)+15,PATINDEX('%Dictator:%',Col)-PATINDEX('%From: Account:%',Col)-16) AS FromAcct,
SUBSTRING (Col,PATINDEX('%Dictator:%',Col)+10,PATINDEX('%To: Account:%',Col)-PATINDEX('%Dictator:%',Col)-11) AS FromDictator,
REVERSE(SUBSTRING(REVERSE(Col),PATINDEX('%:rotatciD%',REVERSE(Col))+10,PATINDEX('%:tnuoccA :oT%',REVERSE(Col))-PATINDEX('%:rotatciD%',REVERSE(Col))-11)) AS ToAcct,
REVERSE(LEFT (REVERSE(Col),PATINDEX('%:rotatciD%',REVERSE(Col))-1)) AS ToDictator
FROM Table

quote:

---

Originally posted by pinkfloyd43



Funny when someone puts data like this into a column and are saying don't worry we will never need to report on it!

---