This means that I had to have a method of easily splitting a string, and then taking the data and grabbing non duplicate data, and ignoring empty strings. This was a considerably more interesting problem than I originally thought it would be when I first approached it.
So the first step is to get a good split string implementation. I went with a common one you can get after searching for a second on Google.
CREATE
--DROP
FUNCTION [dbo].[SplitString]
(
@InputString VARCHAR(MAX), -- Input string
@Delimiter CHAR(1) = ',', -- Delimiter
@RemoveEmpty bit = 1, -- Remove Empty IE
empty string is removed from the output
@RemoveDupes bit = 0 -- Remove Dupes -- Removes duplicates from the list if
executed.
)
RETURNS
@OutList TABLE
(
Position int identity(1,1) -- The position of the returned parsed string
,Value VARCHAR(50) -- The value of
the returned parsed string.
)
AS
BEGIN
--Parse the string and insert each
list item into the @tblList
DECLARE @ListItem varchar(max)
DECLARE @Pos1 int, @Pos2 int, @Length int, @ListLength int
SET @Pos1 = 1
SET @ListLength = LEN(@InputString)
WHILE @Pos1 <= @ListLength
BEGIN
SET @Pos2 = CHARINDEX(@Delimiter,
@InputString, @Pos1)
IF @Pos2 < 0 SET @Pos2 = 0
SET @Length = @Pos2 - @Pos1
IF @Length < 0 SET @Length = 0
IF @Pos2 > 0
BEGIN
SET
@ListItem = SUBSTRING(@InputString, @Pos1, @Length)
SET
@Pos1 = @Pos2 +
1
END
ELSE
BEGIN
SET
@ListItem = SUBSTRING(@InputString, @Pos1, @ListLength)
SET
@Pos1 = @ListLength +
1
END
IF(@RemoveEmpty = 1 and RTRIM(isnull(@ListItem,'')) = '')
BEGIN
CONTINUE;
END
ELSE
IF(@RemoveDupes = 0)
BEGIN
INSERT
@OutList (Value) VALUES(@ListItem)
END
ELSE
BEGIN
INSERT @OutList (Value)
SELECT @ListItem
WHERE NOT EXISTS (SELECT * FROM @OutList where Value =
@ListItem)
END
END
RETURN
END
I then create a problem set of data that I want to split.
tmpexampleid
|
datatoparse
|
1
|
ParseThisValue1,ParseThisValue2,ParseThisValue3
|
2
|
ParseThisValue1,ParseThisValue2,ParseThisValue4
|
3
|
ParseThisValue1,,ParseThisValue2,ParseThisValue4
|
4
|
ParseThisValue1,,ParseThisValue2,ParseThisValue3,ParseThisValue4
|
5
|
|
6
|
,,,,
|
7
|
,ParseThisValue1,,,
|
8
|
,ParseThisValue1,,ParseThisValue2,
|
9
|
,ParseThisValue1,,ParseThisValue2,ParseThisValue3
|
10
|
,ParseThisValue1,
,ParseThisValue2,ParseThisValue3
|
And then I do the string split. Note the following should run as long as SplitString exists in the same database context.
-- EXAMPLE of
RETURN
SELECT * FROM [dbo].[SplitString] (
'test,test2,test3,,,,,,
'
,','
,1
,0)
-- No values
returned if no data to split!
SELECT * FROM [dbo].[SplitString] (
''
,','
,1
,0)
SELECT * FROM [dbo].[SplitString] (
null
,','
,1
,0)
GO
-- This shows all
options turned on, returns a postioned deduped list that removes empty string
values.
IF object_id('tempdb..#tmpexample')
IS NOT NULL
BEGIN
DROP TABLE #tmpexample
END
create table #tmpexample
(
tmpexampleid int identity(1,1)
, datatoparse varchar(255)
)
INSERT INTO #tmpexample
(
datatoparse
)
SELECT
'ParseThisValue1,ParseThisValue2,ParseThisValue3'
UNION ALL
SELECT
'ParseThisValue1,ParseThisValue2,ParseThisValue4'
UNION ALL
SELECT
'ParseThisValue1,,ParseThisValue2,ParseThisValue4'
UNION ALL
SELECT
'ParseThisValue1,,ParseThisValue2,ParseThisValue3,ParseThisValue4'
UNION ALL
SELECT
''
UNION ALL
SELECT
',,,,'
UNION ALL
SELECT
',ParseThisValue1,,,'
UNION ALL
SELECT
',ParseThisValue1,,ParseThisValue2,'
UNION ALL
SELECT
',ParseThisValue1,,ParseThisValue2,ParseThisValue3'
UNION ALL
SELECT
',ParseThisValue1, ,ParseThisValue2,ParseThisValue3'
select * from #tmpexample
;with
cte_parsemap as
(
select 1 as idpos, 'PRIMARY' as namepart
UNION
select 2, 'SECONDARY'
UNION
select 3, 'THIRD---ARY'
) ,
cte_candidates as
(
SELECT *
FROM #tmpexample PPOSRC
--WHERE
-- and
FileID = @FileID
)
, cte_parsenames as
(
select
c.[tmpexampleid],O.Value,cp.namepart
from cte_candidates c
CROSS APPLY
dbo.[SplitString](c.[datatoparse], ',',1,1) AS O
JOIN cte_parsemap cp
on O.Position = cp.idpos
)
, cte_parsed as
(
select pvt.[tmpexampleid],[PRIMARY],[SECONDARY],[THIRD---ARY]
from cte_parsenames c
PIVOT
(
max (Value)
FOR namepart IN
( [PRIMARY],[SECONDARY],[THIRD---ARY])
) AS pvt
)
SELECT *
FROM cte_parsed
GO
Example Output
|
1
|
ParseThisValue1
|
ParseThisValue2
|
ParseThisValue3
|
|
2
|
ParseThisValue1
|
ParseThisValue2
|
ParseThisValue4
|
|
3
|
ParseThisValue1
|
ParseThisValue2
|
ParseThisValue4
|
|
4
|
ParseThisValue1
|
ParseThisValue2
|
ParseThisValue3
|
|
7
|
ParseThisValue1
|
NULL
|
NULL
|
|
8
|
ParseThisValue1
|
ParseThisValue2
|
NULL
|
|
9
|
ParseThisValue1
|
ParseThisValue2
|
ParseThisValue3
|
|
10
|
ParseThisValue1
|
ParseThisValue2
|
ParseThisValue3
|
No comments:
Post a Comment