Hi Mahesh1986,
Refer below sample query.
SQL
CREATE FUNCTION dbo.SplitString(@List NVARCHAR(MAX), @Delemiter1 Varchar(MAX), @Delemiter2 Varchar(MAX))
RETURNS TABLE
AS
RETURN
(
SELECT Item = FirstSet.cnt.value('(./text())[1]', 'nvarchar(4000)')
FROM
(
SELECT x = CONVERT(XML, '<cnt>'
+ REPLACE(REPLACE(@List, ISNULL(@Delemiter1,''), '</cnt><cnt>') , ISNULL(@Delemiter2,''), '</cnt><cnt>')
+ '</cnt>').query('.')
) AS a CROSS APPLY x.nodes('cnt') AS FirstSet(cnt)
);
GO
DECLARE @department AS TABLE(DeptID VARCHAR(20), Dept_Name VARCHAR(20))
INSERT INTO @department VALUES('1','Sales')
INSERT INTO @department VALUES('2','finance')
INSERT INTO @department VALUES('3','IT')
DECLARE @employees AS TABLE(EmpID INT ,EmpName VARCHAR(20),Emp_Departments VARCHAR(50))
INSERT INTO @employees VALUES(1,'Test','1@,2@,3')
DECLARE @str AS VARCHAR(MAX)
SET @str = (SELECT Emp_Departments FROM @employees)
SELECT e.EmpID,
e.EmpName,
d.Dept_Name
FROM @employees e, @department d
WHERE d.DeptID IN(SELECT Item FROM dbo.SplitString(''+@str+'' , '@',','))
Output
| EmpID |
EmpName |
Dept_Name |
| 1 |
Test |
Sales |
| 1 |
Test |
finance |
| 1 |
Test |
IT |
