SQL insert data to other table after split string

Question

I have a table WCA:

ID TYPE    ..
1  *1*3*5*
2  *1*5*
..

Now i want move data to new table WCA_TYPE:

ID WCA_ID TYPE
1    1     1
2    1     3
3    1     5
4    2     1
5    2     5
..

ID here is auto increase.

How to write sql in MS SQL server to split type from old table to multi type & insert it into new table.

HotblackDesiato · Answer 1 · 2013-08-16T08:26:44.377

You could use a recursive cte - e.g.

CREATE TABLE #WCA_TYPE
(ID         INT     IDENTITY(1, 1)  PRIMARY KEY
,WCA_ID     INT
,TYPE       INT);

WITH sampleData(WCA_ID, TYPE) AS
(
    SELECT
     *
    FROM (  VALUES   ('1', '1*3*5')
                    ,('2', '1*5')
         ) nTab(nCol1, nCol2)
)
,rep(WCA_ID, item, delim) AS
(
    SELECT
     WCA_ID
    ,TYPE item
     ,'*' delim
    FROM sampleData

    UNION ALL

    SELECT
     WCA_ID
    ,LEFT(item, CHARINDEX(delim, item, 1) - 1) item
    ,delim
    FROM rep
    WHERE (CHARINDEX(delim, item, 1) > 0)

    UNION ALL

    SELECT
     WCA_ID
    ,RIGHT(item, LEN(item) - CHARINDEX(delim, item, 1)) item
    ,delim
    FROM rep
    WHERE (CHARINDEX(delim, item, 1) > 0)
)
INSERT #WCA_TYPE
(TYPE
,WCA_ID)
SELECT
 item
,WCA_ID
FROM rep
WHERE (CHARINDEX(delim, item, 1) = 0)
ORDER BY WCA_ID
OPTION (MAXRECURSION 0);

SELECT * FROM #WCA_TYPE;

in WCA table, it have many records with the same format for TYPE column. Is your script only for a specific record? — QuanNH, Aug 16 '13 at 07:57

score 2 · Answer 2 · answered Aug 16 '13 at 07:59

DECLARE @CurID INT, @MaxID INT, @t VARCHAR(200)

SELECT @CurID = 1, @MaxID = MAX(ID) FROM WCA

WHILE @CurID <= @MaxID
BEGIN
  SELECT @t = TYPE
  FROM WCA
  WHERE ID = @CurID

  ;WITH Pieces([Pos], [start], [stop]) AS (
      SELECT 1, 1, CHARINDEX('*', @t)
      UNION ALL
      SELECT [Pos] + 1, [stop] + 1, CHARINDEX('*', @t, [stop] + 1)
      FROM Pieces
      WHERE [stop] > 0
    )
    INSERT INTO WCA_TYPE(WCA_ID, TYPE)
    SELECT @CurID, T.Value
    FROM
    ( SELECT [Pos], SUBSTRING(@t, start, CASE WHEN [stop] > 0 THEN [stop]-[start] ELSE 4000 END) AS [Value]
      FROM Pieces
    ) T 
    WHERE T.Value <> '' 


  SET @CurID = @CurID + 1
END

SELECT *
FROM WCA_TYPE

look this fiddle

score 2 · Answer 3 · edited Aug 16 '13 at 08:16

2

Assuming the your type column always have 3 digits you can use this.

INSERT INTO wca_type (WCA_id,type)  
   SELECT 1,SUBSTRING(CONVERT(nvarchar(MAX),type),1,1) FROM wca    
   UNION ALL   
   SELECT 1,SUBSTRING(CONVERT(nvarchar(MAX),type),2,1) FROM wca  
   UNION ALL   
   SELECT 1,SUBSTRING(CONVERT(nvarchar(MAX),type),3,1) FROM wca

edited Aug 16 '13 at 08:16

Noel

10,152
30
45
67

answered Aug 16 '13 at 08:01

zxc

1,504
3
13
32

it may have multi digits – QuanNH Aug 16 '13 at 09:08

score 1 · Accepted Answer · edited Aug 16 '13 at 08:15

WITH CTE AS
(
  select id,Type,0 as startPos, 
          CHARINDEX('*',TYPE)-1 as endPos from WCA
  UNION ALL
  select id,Type,endPos+2 as startPos, 
          CHARINDEX('*',TYPE,endPos+2)-1 as endPos from CTE
  where CHARINDEX('*',TYPE,endPos+2)>0
)

INSERT INTO WCA_TYPE (WCA_ID, TYPE)
select ID,
        CASE WHEN EndPos>0 
        THEN
        Substring(Type,StartPos,EndPos-StartPos+1) 
        else
        Type
        end as Type
from CTE 
where EndPos<>0

SQLFiddle Select demo

SQL insert data to other table after split string

4 Answers4

Linked