how to split and show in new row in sql

Khargesh Rajput

13y1.5k

How to Split aafter comma and display in new Row
i have empid as 1,2,5,3,4
and i want to split and display as
1
2
5
3
4
with emnpname
1=a
2=b
3=c
.........................
please help

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi13y ago
Accepted answer
hi,
if you are SQL server 2008 and above user, split function is inbuilt.
http://technet.microsoft.com/en-us/library/aa496058%28v=sql.80%29.aspx
you can also modify this function as per your requirement.
0
Jaganathan Bantheswaran13y ago
What you want to join?
0
Puneet Gupta13y ago
Here is the link, I hope you will get the solution

http://www.webtechminer.com/split-function-in-sql-server-to-break-comma-separated-string/

Thanks,
Puneet Gupta
0
Khargesh Rajput13y ago
i have this function

ALTER FUNCTION [dbo].[Split]
(
    @RowData nvarchar(2000),
    @SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
    Id int identity(1,1),
    Data nvarchar(100)
)
AS
BEGIN
    Declare @Cnt int
    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
        Insert Into @RtnValue (data)
        Select
            Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
        Set @Cnt = @Cnt + 1
    End

    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
END

but how to use with query in join
0