The requirement is to convert the delimited column into rows

Input data and required output details are given below

Output:-

Download the Script Split Delimited Column using XML Or UDF

T-SQL -Split a delimited column data using XML

DECLARE @DemoTable table
 (
 Eno int,
 Ename char(10),
 EsLoc char(10),
 EDept varchar(20))
 
INSERT INTO @DemoTable values(1,'Prashanth','NJ','1,2,3,4,5')
INSERT INTO @DemoTable values(2,'Jayaram','NY','1,2,3')

SELECT A.ENo, A.EName , A.EsLoc,
 Split.a.value('.', 'VARCHAR(100)') AS Dept 
 FROM (SELECT ENo,EName,EsLoc,
 CAST ('<M>' + REPLACE(EDept, ',', '</M><M>') + '</M>' AS XML) AS String 
 FROM @DemoTable) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

OR

Using UDF-

Reference Link :- http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html 

CREATE FUNCTION ParseValues
(@String varchar(8000), @Delimiter varchar(10) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(8000))
AS
BEGIN
DECLARE @Value varchar(100)
WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END

DECLARE @DemoTable table
 (
 Eno int,
 Ename char(10),
 EsLoc char(10),
 EDept varchar(20))
 
INSERT INTO @DemoTable values(1,'Prashanth','NJ','1,2,3,4,5')
INSERT INTO @DemoTable values(2,'Jayaram','NY','1,2,3')

SELECT t.Eno, t.Ename,t.EsLoc,f.Val Dept
FROM @DemoTable t
CROSS APPLY dbo.ParseValues(t.EDept,',')f

Output:-