how to get value from comma delimited String? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to get value from comma delimited String?

Author

Topic

rahmoh
Starting Member

1 Post

Posted - 2007-08-21 : 04:37:12

i have the following table

MasterDept
-----------
DeptId | DeptName
-------------------
100 | Othro
104 | Urology

EmpDetails
---------------
EmpId | DeptId
105 100
107 104

AssignEmployee
--------------
ClientId | EmpId
-----------------
1 | 105,107

I have the above tables where empdetails are stored in separate table with deptId . to assign a employees to a client, i have stored clientid and comma separated empId(string) in table assignemployee. I need a query which should display the following result.

ClientId | EmpId | DeptId | DeptName
-------------------------------------
1 105 100 othro
1 107 104 Urology

Is this is possible to get the above values with a query? Pls give me example or query.

peter@tyler-online.co.uk
Starting Member

1 Post

Posted - 2007-08-21 : 05:54:59

I have a split function which can split out any dilimited string. Using the split function inside a cursor (sorry

I cannot think of a better way) you can build a tamp table with all the assigned employees for a customer, then use this to return your data.

**** NOTE *****
I have an example below, please note that it will create a udf called uf_Split so ensure you have not got a function of that name already!

-- ===================================================
--Create Tables for demo
-- ===================================================
CREATE Table #MasterDept
(
DeptId INT
,DeptName VARCHAR(50)
)
INSERT INTO #MasterDept
VALUES(100, 'Othro')
INSERT INTO #MasterDept
VALUES(104, 'Urology')

CREATE Table #EmpDetails
(
EmpId INT
,DeptId INT
)
INSERT INTO #EmpDetails
VALUES(105, 100)
INSERT INTO #EmpDetails
VALUES(107, 104)

CREATE Table #AssignEmployee
(
ClientId INT
,EmpId VARCHAR(250)
)
INSERT INTO #AssignEmployee
VALUES(1, '105,107')

-- ===================================================
--End Create Tables for Demo
-- ===================================================

-- ===================================================
--Create Split Function
-- ===================================================
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uf_Split]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[uf_Split]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[uf_Split](@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
@value varchar(8000),
@bcontinue bit,
@iStrike smallint,
@iDelimlength tinyint

IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
BEGIN
WHILE @bcontinue = 1
BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.

IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END

--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))

END
ELSE
BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText)>1
BEGIN
SET @value = SUBSTRING(@sText,1,1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @idx = @idx+1
SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)

END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx, value)
VALUES (@idx, @sText)
SET @bcontinue = 0
END
END

END

RETURN
END

GO

-- ===================================================
--END Split Function
-- ===================================================

--Now we can start the code to return
--ClientId | EmpId | DeptId | DeptName
--------------------------------------
--1 105 100 othro
--1 107 104 Urology

--Create Temp table to stor extracted Assigned Employees
create table #tmpAssignEmployee
(ClientId INT
,EmpId INT
)

-- Create Cursor to fill temp Assigned employees table
DECLARE AssignedEmpCursor CURSOR
READ_ONLY
FOR SELECT ClientId, EmpID FROM #AssignEmployee

DECLARE @ClientId INT
DECLARE @EmpID varchar(50)

OPEN AssignedEmpCursor

FETCH NEXT FROM AssignedEmpCursor INTO @ClientId, @EmpID
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- Insert into tmpAssignEmployee using split function
INSERT INTO #tmpAssignEmployee
SELECT @ClientID, [Value] from uf_Split(@EmpID, ',')
END
FETCH NEXT FROM AssignedEmpCursor INTO @ClientId, @EmpID
END
CLOSE AssignedEmpCursor
DEALLOCATE AssignedEmpCursor
GO

--Now return the results
SELECT ae.ClientID, ed.EmpID, md.DeptID, md.DeptName
FROM #EmpDetails ed
INNER JOIN #tmpAssignEmployee ae
ON ae.EmpID = ed.EmpID
INNER JOIN #MasterDept md
ON md.DeptID = ed.DeptID

--Clean up
DROP Table #MasterDept
DROP Table #EmpDetails
DROP Table #AssignEmployee
DROP Table #tmpAssignEmployee

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-21 : 06:08:19

Also, Do google search on Normalization

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 06:21:24

Madhi is right. But there is a way easier solution than the above.

SELECT		ae.ClientID,
		ed.EmpID,
		ed.DeptID,
		md.Deptname
FROM		AssignEmployee AS ae
INNER JOIN	EmpDetails AS ed ON ',' + CONVERT(VARCHAR, ae.EmpID) + ',' LIKE '%,' + CONVERT(VARCHAR, ed.EmpID) + ',%'
INNER JOIN	MasterDept AS md ON md.DeptID = ed.DeptID

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-21 : 07:18:19

Well. I prefer specifying column size, though if omitted, it would take 30

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources