CODEWITHSUNDEEP
sqlsql-server
ASH
ASH

Reputation: 20362

How can I parse one field into multiple fields in the same table?

I am trying to parse a field based on a delimiter, in this case it is a '|'. Then, merge everything into other fields (Parse1, Parse2, etc.) in the same table. The code below seems to be pretty close, but it only parses correctly in my TempTable. For some reason, it does not update the Parse1, Parse2, Parse3, etc., in the FinalTable

SELECT    DISTINCT
          split.a.value ('/A[1]', 'VARCHAR(MAX)') [Piece1],
          split.a.value ('/A[2]', 'VARCHAR(MAX)') [Piece2],
          split.a.value ('/A[3]', 'VARCHAR(MAX)') [Piece3],
          split.a.value ('/A[4]', 'VARCHAR(MAX)') [Piece4],
          split.a.value ('/A[5]', 'VARCHAR(MAX)') [Piece5],  
          split.a.value ('/A[6]', 'VARCHAR(MAX)') [Piece6],  
          split.a.value ('/A[7]', 'VARCHAR(MAX)') [Piece7]
 Field1,
 Field2,
 Field3
FROM [TempTable]
(
SELECT      CAST('<A>' + REPLACE(SrcID, '|', '</A><A>') + '</A>' AS XML) AS Data, Field1, Field2, Field3
FROM  dbo.FinalTable
) a cross apply Data.nodes('/A') AS split(a)

Does anyone have any idea what is wrong with this?

Upvotes: 0

Views: 86

Answers (2)

Nolan Shang
Nolan Shang

Reputation: 2328

I tried below script, it should works.

    IF object_id('tempdb..#FinalTable') is not null drop table #FinalTable
    create table #FinalTable(SrcID varchar(max),Field1 int ,Field2 int ,Field3 int)
    insert into #FinalTable(SrcID,Field1,Field2,Field3)
    select 'a|b|c|d|e|f|g|h|i|j',1,2,3 union all
    select 'aa|sb|cc|bdn|ce|ffg|hjg|kh|ii|kj',1,2,3

    SELECT    DISTINCT
              split.a.value ('/A[1]', 'VARCHAR(MAX)') [Piece1],
              split.a.value ('/A[2]', 'VARCHAR(MAX)') [Piece2],
              split.a.value ('/A[3]', 'VARCHAR(MAX)') [Piece3],
              split.a.value ('/A[4]', 'VARCHAR(MAX)') [Piece4],
              split.a.value ('/A[5]', 'VARCHAR(MAX)') [Piece5],  
              split.a.value ('/A[6]', 'VARCHAR(MAX)') [Piece6],  
              split.a.value ('/A[7]', 'VARCHAR(MAX)') [Piece7],
     Field1,
     Field2,
     Field3
    FROM 
    (
    SELECT  CAST('<A>' + REPLACE(SrcID, '|', '</A><A>') + '</A>' AS XML) AS Data, Field1, Field2, Field3
    FROM  #FinalTable
    ) a cross apply Data.nodes('/A') AS split(a)

+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| Piece1 | Piece2 | Piece3 | Piece4 | Piece5 | Piece6 | Piece7 | Field1 | Field2 | Field3 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| a      | b      | c      | d      | e      | f      | g      | 1      | 2      | 3      |
| aa     | sb     | cc     | bdn    | ce     | ffg    | hjg    | 1      | 2      | 3      |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+

Upvotes: 1

ASH
ASH

Reputation: 20362

I ended up using this.

CREATE FUNCTION [dbo].[SplitIndex](@Delimiter varchar(20) = ' ', @Search varchar(max), @index int)
RETURNS varchar(max)
AS
BEGIN
      DECLARE @ix int,
                  @pos int,
                @rt varchar(max)

      DECLARE @tb TABLE (Val varchar(max), id int identity(1,1))

      SET @ix = 1
      SET @pos = 1


      WHILE @ix <= LEN(@search) + 1 BEGIN

            SET @ix = CHARINDEX(@Delimiter, @Search, @ix)

            IF @ix = 0
                  SET @ix = LEN(@Search)
            ELSE
                  SET @ix = @ix - 1

            INSERT INTO @tb
            SELECT SUBSTRING(@Search, @pos, @ix - @pos + 1)

            SET @ix = @ix + 2
            SET @pos = @ix
      END

      SELECT @Rt = Val FROM @Tb WHERE id = @index
      RETURN @Rt     
END


SELECT dbo.SplitIndex(' ', 'hello big wide world', 1)
SELECT dbo.SplitIndex(' ', 'hello big wide world', 2)
SELECT dbo.SplitIndex(' ', 'hello big wide world', 3)
SELECT dbo.SplitIndex(' ', 'hello big wide world', 4)

It works excellent!!

Upvotes: 0

Related Questions