How to Split String value field in many records

[khan007]

Hi i am SQL 2k.

I have a table Employee with a field department .
Department is a string field possessing data seperated by comma. like
PT, AUD, OT, STR, PBSTRA, XLP, SP, SB, EI

The size of each value is not fixed.

My question is i need to create a view to split this feild into seperate value.

For example in table Employee
The data appears like that:

Employee# lastname Department
xxx Joe SP
yyy jim SP, AUD, BTI
zzz Tim PT, OT
bbb Sho BP, PT, SP, AU

Now What I am Looking for is:

Employee# lastname Department Dep_Split
xxx Joe SP SP
yyy jim SP, AUD, BTI SP
yyy jim SP, AUD, BTI AUD
yyy jim SP, AUD, BTI BTI
zzz Tim PT, OT PT
zzz Tim PT, OT OT
bbb Sho BP, PT, SP, AU BP
bbb Sho BP, PT, SP, AU PT
bbb Sho BP, PT, SP, AU SP
bbb Sho BP, PT, SP, AU AU

Thanks in Advance

 

[SQLSister]

I would strongly suggest you redesign this database. You should NEVER store multiple pieces of informatin in one filed becasue it is then difficult to get the information back out as you are finding. The dapartment would correctly be handled with a related table to store the department and peron's id.

That said, I realize that you are stuck with this right now and even to convert it to a correct design you will need to havea method of splitting the data. Since your data seems to be split with commas, I would suggest that you will have to loop through the data and pull the information out to a temp table (if you aren't doing a redeisgn) or a the new table you set up to store the department information for each employee. So you need to look up how to do while loops or cursors.

Then you will need to use the Len(), Charindex (or Pathindex), substring functions to pull out exactly the data you want. You will have to loop through each record until you have found and split out each department, then move tothe next record and do the same thing.

Best to do this with a user-defined function. If you Google for it you will find one from Microsoft called fn_split which should help you out.

 

[khan007]

ok i got it, what you mentioned.
But can you let me know by an example how to pull out data for any above patient by using eiher substring or charindex,ans save in temp table.
thanks in advance

 

[ScottieW]

Sorry I havent time to think through your problem in detail but below is a proc that will split out the various values in a field if you specify the delimiter.

Hope it is of some help.

CREATE PROCEDURE Split(@string varchar(2000),@delimiter varchar(10))
as
DECLARE @str varchar(2000)
SET @str = @string
DECLARE @d varchar(10)
SET @d = @delimiter
DECLARE @start int
DECLARE @len int
DECLARE @pos int
DECLARE @pos1 int
DECLARE @val int
SET @pos = 1
SET @start = charindex(@d,@str,@pos)
SET @len = len(@str)
CREATE TABLE #temp (val int)
WHILE @start <= @len
BEGIN
IF (@pos = 1)
BEGIN
SET @val = substring(@str,1,@start-1)
INSERT INTO #temp values(@val)
SET @pos = @pos + 1
END
ELSE
BEGIN
SET @pos1 = charindex(@d,@str,@start+1)
IF @pos1 = 0
BEGIN
SET @val = substring(@str,@start+1,@len-@start)
INSERT INTO #temp values(@val)
break;
END
SET @val = substring(@str,@start+1,@pos1-@start-1)
INSERT INTO #temp values(@val)
SET @start = @pos1
SET @pos1 = 0
END
END
SELECT * FROM #temp
DROP TABLE #temp

--EXAMPLE

--exec Split '123,456,789,101112',','

-- exec split '123 456 789',' '

-- exec split '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16',','

 

[khan007]

Thanks

Hopefully i will resolve my issue by issue this script.

Thanks again .

 

[khan007]

Thanks

Hopefully i will resolve my issue using this script.

Thanks again .