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

Question

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?

Can you share some sample data, current output and required output? thanks. — ViKiNG
– ViKiNG, Commented Nov 23, 2017 at 0:54
Are you sure you've sent us all the code? This code does nothing but show you results - there is no command to actually store or update any data. — DancingFool
– DancingFool, Commented Nov 23, 2017 at 1:41

Nolan Shang · Accepted Answer · 2017-11-28 19:38:47Z

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      |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+

ASH · Accepted Answer · 2017-11-25 14:00:53Z

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!!

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related