quote:

---

Originally posted by flamblaster

Hey folks,

Vis just answered my issue yesterday on parsename; If you see the post, I actually wound up using Charindex by Vis' suggestion. Vis (or anyone else who might like to input), I was wondering, would something like the Substring(Reverse(Charindex... syntax work for the following? I'm getting partial results, but it's filtering a lot out that I don't want to filter:

DATASET:
Table: Clients
Fields: ClientID, Disability EDIT: (Disability is varchar(30) )

When I run a basic select query:

SELECT ClientID, Disability
FROM Clients

The sample results would be:

ClientID***Disability
1 *** 1,B,C,F
2 *** 3,G,H
3 *** 1,D,E

My desired output is:

ClientID***Disability
1 ***1
1 ***B
1 ***C
1 ***F
2 ***3
2 ***G
2 ***H
3 ***1
3 ***D
3 ***E

I don't think the Charindex will work because the only separators will ever be a comma or a space. And again, I'm not able to declare the cursor, create stored procedures, drop table, create view or anything like that in the interface that I have.

Thanks!
Craig

---

CREATE FUNCTION ParseValues  
(@String varchar(8000),
@Delimiter char(1)  
)  
RETURNS @RESULTS TABLE  
(ID int identity(1,1),  
Val varchar(100) 
)  
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

SELECT c.ClientID,f.Val
FROM Clients c
CROSS APPLY dbo.ParseValues(c.Disability) f
ORDER BY f.ID

quote:

---

Originally posted by flamblaster

Hi Vis,
As always, thanks for the reply. I understand the post, but am unable to create that function...Suppose there's not a work around using
a Select statement alone. That's ok though...I thought maybe there was similar logic from the example you answered yesterday.

Thanks again!
Craig

---