COMBINE MULTIPLE SPIT VALUES IN SQL SERVER

Think if we have a string like below, it is actually a combination of name and state of some users.

Like arun is the name and kerala is the state. And the comma character is separate each user

'arun:kerala,anu:tamilnadu,sruthi:mumbai,prasanth:andra,sathya:kasmir

,bijoy:madyapredesh'

We want to split it using ‘,’ and ‘:’ so the results will be like

Arun

Kerala

Anu

Tamilnadu

Sruthi

Mumbai

Prasanth

Andra

Sathya

Kasmir

Bijoy

Madyapredesh

But what we need is we want to combine the results based on each user

Like below

NAME        STATE

-----------------------------

arun        kerala

anu         tamilnadu

sruthi      mumbai

prasanth    andra

sathya      kasmir

bijoy       madyapredesh

We can look how we can do it in SQL SERVER, one of the simplest method I have included here

IF OBJECT_ID('tempdb..#ResultTbl') IS NOT NULL

      DROP TABLE #ResultTbl

GO

CREATE TABLE #ResultTbl (

      NAME NVARCHAR(500)

      ,STATE NVARCHAR(500)

      )

DECLARE @SourceTbl TABLE (val NVARCHAR(500))

INSERT INTO @SourceTbl (val)

VALUES ('arun:kerala,anu:tamilnadu,sruthi:mumbai,prasanth:andra,sathya:kasmir,bijoy:madyapredesh');

WITH CTE

AS (

      SELECT Split.a.value('.', 'NVARCHAR(100)') AS Returndata

      FROM (

            SELECT CAST('<Mydata>' + REPLACE([val], ',', '</Mydata><Mydata>') + '</Mydata>' AS XML) AS Returndata

            FROM @SourceTbl

            ) AS A

      CROSS APPLY Returndata.nodes('/Mydata') AS Split(a)

      )

INSERT INTO #ResultTbl

SELECT SUBSTRING(Returndata, 0, CHARINDEX(':', Returndata))

      ,REVERSE(SUBSTRING(reverse(Returndata), 0, CHARINDEX(':', reverse(Returndata))))

FROM cte

SELECT *

FROM #ResultTbl

The result will be

NAME        STATE

-----------------------------

arun        kerala

anu         tamilnadu

sruthi      mumbai

prasanth    andra

sathya      kasmir

bijoy       madyapredesh

If you do not have split function then use the below split function

CREATE FUNCTION dbo.Split (

      @String VARCHAR(8000)

      ,@Delimiter CHAR(1)

      )

RETURNS @temptable TABLE (items VARCHAR(8000))

AS

BEGIN

      DECLARE @idx INT

      DECLARE @slice VARCHAR(8000)

      SELECT @idx = 1

      IF len(@String) < 1

            OR @String IS NULL

            RETURN

      WHILE @idx != 0

      BEGIN

            SET @idx = charindex(@Delimiter, @String)

            IF @idx != 0

                  SET @slice = left(@String, @idx - 1)

            ELSE

                  SET @slice = @String

            IF (len(@slice) > 0)

                  INSERT INTO @temptable (Items)

                  VALUES (@slice)

            SET @String = right(@String, len(@String) - @idx)

            IF len(@String) = 0

                  BREAK

      END

      RETURN

END