April 3, 2020 at 1:52 am
Hello All,
I have the following table
Table A
ID DocNumber InternalDocnumber date DocType Descriptin NameG NameGR NumberPage
1 12345 566767 12/23/2020 3 thisisaTest test1, test2, test3 test6, test4 1
2 23456 566768 12/23/2020 3 thisisaTest2 test4, test5, test6 test9, test4 2
I have around 5000 rows. I want to do the comma based split the nameG and NameGR and insert them in the same table or new table again. so for e.g
ID DocNumber InternalDocnumber date DocType Description NameG NameGR NumberPage
1 12345 566767 12/23/2020 3 thisisaTest test1 test6 1
1 12345 566767 12/23/2020 3 thisisaTest test2 test4 1
1 12345 566767 12/23/2020 3 thisisaTest test3 NULL 1
2 23456 566768 12/23/2020 3 thisisaTest2 test4 test9 2
2 23456 566768 12/23/2020 3 thisisaTest2 test5 test4 2
2 23456 566768 12/23/2020 3 thisisaTest2 test6 NULL 2
I already got the split function . Here is what I have right now, but it is not working:
INSERT INTO [dbo].[Table B]
([DocNumber]
,[InternalDocNumber]
,[Date]
,[DocType]
,[Description]
,[NameG]
,[nameGR]
,[NumberPage]
select
DocNumber,
InternalDocNumber,
Date,
DocType,
Description,
dbo.SplitString([NameG], ','),
dbo.SplitString([NameGR], ','),
NumberPage,
from Table A
GO
Below is the split function that I got from internet:
USE [db_recload]
GO
/****** Object: UserDefinedFunction [dbo].[SplitString] Script Date: 4/2/2020 6:54:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[SplitString]
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
Item NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END
WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)
INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END
RETURN
END
Below the create table script
CREATE TABLE [dbo].[TABLEA](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DocNumber] [varchar](50) NULL,
[InternalDocNumber] [varchar](50) NULL,
[Date] [varchar](50) NULL,
[DocType] [varchar](50) NULL,
[Description] [varchar](50) NULL,
[NameG] [varchar](max) NULL,
[NameGR] [varchar](max) NULL,
[NumberPages] [varchar](50) NULL,
CONSTRAINT [PK_Id] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Below is the insert script:
INSERT INTO [dbo].[TABLEA]
([DocNumber]
,[InternalDocNumber]
,[Date]
,[DocType]
,[Description]
,[NameG]
,[NameGR]
,[NumberPages])
VALUES
(1
,1235
,'12/23/2020'
,3
,'this is a test'
,'test1, test2, test3'
,'test4,test5'
,1)
GO
USE [db_recload]
GO
INSERT INTO [dbo].[TABLEA]
([DocNumber]
,[InternalDocNumber]
,[Date]
,[DocType]
,[Description]
,[NameG]
,[NameGR]
,[NumberPages])
VALUES
(2
,3456
,'12/24/2020'
,3
,'this is a test1'
,'test4, test5, test6'
,'test7,test8'
,2)
GO
April 3, 2020 at 2:19 am
Use a better splitter function? I used Jeff Moden's DelimitedSplit8K which is here ...
Setup (this is the part you were supposed to do):
use tempdb;
go
CREATE TABLE #BadData (
ID INT,
DocNumber INT,
InternalNumber INT,
DocumentDate DATE,
DocType TINYINT,
DocDescription VARCHAR(50),
NameG VARCHAR(300),
NameGR VARCHAR(500),
NumberPage TINYINT);
GO
INSERT INTO #BadData VALUES
(1, 12345, 566767, '12/23/2020', 3, 'thisisaTest', 'test1, test2, test3','test6, test4',1),
(2, 23456, 566768, '12/23/2020', 3, 'thisisaTest2', 'test4, test5, test6', 'test9, test4', 2);
Solution:
INSERT INTO #GoodTable (DocNumber, InternalNumber, DocumentDate, DocDescription, GItem, GRItem)
SELECT ID
, DocNumber
, InternalNumber
, DocumentDate
, DocDescription
, TRIM(caNameG.Item) AS G_Item
, TRIM(caNameGR.Item) AS GR_Item
FROM #BadData bd
CROSS APPLY Teest.dbo.DelimitedSplit8K(bd.NameG,',') caNameG
CROSS APPLY Teest.dbo.DelimitedSplit8K(bd.NameGR,',') caNameGR;
Output:
ID DocNumber InternalNumber DocumentDate DocDescription G_Item GR_Item
1 12345 566767 2020-12-23 thisisaTest test1 test6
1 12345 566767 2020-12-23 thisisaTest test1 test4
1 12345 566767 2020-12-23 thisisaTest test2 test6
1 12345 566767 2020-12-23 thisisaTest test2 test4
1 12345 566767 2020-12-23 thisisaTest test3 test6
1 12345 566767 2020-12-23 thisisaTest test3 test4
2 23456 566768 2020-12-23 thisisaTest2 test4 test9
2 23456 566768 2020-12-23 thisisaTest2 test4 test4
2 23456 566768 2020-12-23 thisisaTest2 test5 test9
2 23456 566768 2020-12-23 thisisaTest2 test5 test4
2 23456 566768 2020-12-23 thisisaTest2 test6 test9
2 23456 566768 2020-12-23 thisisaTest2 test6 test4
April 3, 2020 at 2:36 am
I posted the create table and insert script above. I will use your split function
April 3, 2020 at 5:15 am
Thanks for the query. Your query is returning this:
1 12345 566767 2020-12-23 thisisaTest test1 test6
1 12345 566767 2020-12-23 thisisaTest test1 test4
1 12345 566767 2020-12-23 thisisaTest test2 test6
I want something like this:
1 12345 566767 2020-12-23 thisisaTest test1 test6
1 12345 566767 2020-12-23 thisisaTest test2 test4
1 12345 566767 2020-12-23 thisisaTest test3 NULL or empty string
I don't want Test1 or Test2 or Test3 to come twice.
April 3, 2020 at 6:33 am
So you mean you're trying to UNION the two split results? Like this? the short version of union is
SELECT f1, f2, f3
FROM t1
UNION ALL
SELECT f4,f5,f6
FROM t2;
So your query would be something like this:
SELECT ID
, DocNumber
, InternalNumber
, DocumentDate
, DocDescription
, TRIM(caNameG.Item) AS G_Item
-- , TRIM(caNameGR.Item) AS GR_Item
FROM #BadData bd
CROSS APPLY Teest.dbo.DelimitedSplit8K(bd.NameG,',') caNameG
UNION ALL
SELECT ID
, DocNumber
, InternalNumber
, DocumentDate
, DocDescription
, TRIM(caNameGR.Item)
FROM #BadData bd
CROSS APPLY Teest.dbo.DelimitedSplit8K(bd.NameGR,',') caNameGR
Not perfect, but should give you some ideas.
April 3, 2020 at 7:07 am
I think this is what the OP desires
select ID
, DocNumber
, InternalNumber
, DocumentDate
, DocDescription
, t.G_Item
, t.GR_Item
from #BadData bd
outer apply (select ltrim(rtrim(caNameG.Item)) AS G_Item
, ltrim(rtrim(caNameGR.Item)) AS GR_Item
from dbo.DelimitedSplit8K(bd.NameG,',') caNameG
full outer join dbo.DelimitedSplit8K(bd.NameGR,',') caNameGR
on caNameG.Itemnumber = caNameGR.ItemNumber
) t
April 3, 2020 at 7:16 am
I totally missed that full outer join. =(
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply