Splitting a dynamic string

Question

I need help parsing a dynamic string(see below). This string can change to only have one set of values to two, to three(like the one below).

Raw String:

valueA=valueB=valueC=valueD==valueE&valueA=valueB=valueC=valueD==valueE&valueA=valueB=valueC=valueD==valueE

End Result: VelueB, ValueB, ValueB

I have been able to extract valueA using the STUFF and CHARINDEX function(see below) but I'm having difficulty just getting valueB. I have also tried doing it in ssrs using the split function but getting nowhere.

STUFF(( SELECT  ',' + ' ' + SUBSTRING(param, 1,
                                              CHARINDEX('=', param) - 1)
                FROM    dbo.Fn_mvparam(column_a, '&') AS fm
              FOR
                XML PATH('')
              ), 1, 1, ' ')

possible duplicate of [Tsql split string](http://stackoverflow.com/questions/10914576/tsql-split-string) — Stefan Steiger, Jul 29 '14 at 20:26
I looked at those and from what I understand the values are static. my string can change so it doesn't really solve my issue. I changed the title to be more specific. thanks! — D-ONE, Jul 29 '14 at 20:38
That what I need the output to be not what I'm getting. thanks! — D-ONE, Jul 29 '14 at 21:02
@D-ONE I have written a function that may help in your request. Hope it helps. — Gouri Shankar Aechoor, Jul 30 '14 at 03:55
@Gidil I'm not sure what you mean why, that just what I need in the report. There is a column that has this string and the string can be a variation of the above. Meaning, it could be valueA=valueB=valueC=valueD==valueE&valueA=valueB=valueC=valueD==valueE&valueA=valueB=valueC=valueD==valueE or valueA=valueB=valueC=valueD==valueE&valueA=valueB=valueC=valueD==valueE or valueA=valueB=valueC=valueD==valueE The request is that in one column they only want valueB extracted. in a different column, they want just valueA extracted. Hope that answers your question... — D-ONE, Jul 30 '14 at 13:35

score 1 · Accepted Answer · answered Jul 30 '14 at 03:46

This should do the trick for you. Hope its useful to you

CREATE FUNCTION uft_DoubleSplitter 
(   
    -- Add the parameters for the function here
    @String VARCHAR(4000), 
    @Splitter1 CHAR,
    @Splitter2 CHAR
)
RETURNS @Result TABLE (Id INT,MId INT,SValue VARCHAR(4000))
AS
BEGIN
DECLARE @FResult TABLE(Id INT IDENTITY(1, 1),
                   SValue VARCHAR(4000))
DECLARE @SResult TABLE(Id INT IDENTITY(1, 1),
                   MId INT,
                   SValue VARCHAR(4000))
SET @String = @String+@Splitter1

WHILE CHARINDEX(@Splitter1, @String) > 0
    BEGIN
       DECLARE @WorkingString VARCHAR(4000) = NULL

       SET @WorkingString = SUBSTRING(@String, 1, CHARINDEX(@Splitter1, @String) - 1)
       --Print @workingString

       INSERT INTO @FResult
       SELECT CASE
            WHEN @WorkingString = '' THEN NULL
            ELSE @WorkingString
            END

       SET @String = SUBSTRING(@String, LEN(@WorkingString) + 2, LEN(@String))

    END
IF ISNULL(@Splitter2, '') != ''
    BEGIN
       DECLARE @OStartLoop INT
       DECLARE @OEndLoop INT

       SELECT @OStartLoop = MIN(Id),
            @OEndLoop = MAX(Id)
       FROM @FResult

       WHILE @OStartLoop <= @OEndLoop
          BEGIN
             DECLARE @iString VARCHAR(4000)
             DECLARE @iMId INT

             SELECT @iString = SValue+@Splitter2,
                   @iMId = Id
             FROM @FResult
             WHERE Id = @OStartLoop

             WHILE CHARINDEX(@Splitter2, @iString) > 0
                BEGIN
                    DECLARE @iWorkingString VARCHAR(4000) = NULL

                    SET @IWorkingString = SUBSTRING(@iString, 1, CHARINDEX(@Splitter2, @iString) - 1)

                    INSERT INTO @SResult
                    SELECT @iMId,
                         CASE
                         WHEN @iWorkingString = '' THEN NULL
                         ELSE @iWorkingString
                         END

                    SET @iString = SUBSTRING(@iString, LEN(@iWorkingString) + 2, LEN(@iString))

                END

             SET @OStartLoop = @OStartLoop + 1
          END
       INSERT INTO @Result
       SELECT MId AS PrimarySplitID,
            ROW_NUMBER() OVER (PARTITION BY MId ORDER BY Mid, Id) AS SecondarySplitID ,
            SValue
       FROM @SResult
    END
ELSE
    BEGIN
       INSERT INTO @Result
       SELECT Id AS PrimarySplitID,
            NULL AS SecondarySplitID,
            SValue
       FROM @FResult
    END
RETURN

Usage:

--FirstSplit
SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&',NULL)

--Second Split
SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&','=')

Scenario Answer:

SELECT fn.SValue
FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===', '&', '=')AS fn
WHERE fn.mid = 2

Deleted previous comment as I was able to incorporate it into my script. The final issue I'm having now is that I'm getting this extra characters added to my result set that I need removed SCRIPT: STUFF(( SELECT svalue FROM dbo.F_DoubleSplitter(column_a, '&', '=') AS fm WHERE fm.mid=2 FOR XML PATH('') ), 1, 1, ' ') RESULT SET: 'svalue>1225' — D-ONE, Jul 30 '14 at 15:29
Please do this STUFF(( SELECT ','+svalue FROM dbo.F_DoubleSplitter(column_a, '&', '=') AS fm WHERE fm.mid=2 FOR XML PATH('') ), 1, 1, '') — Gouri Shankar Aechoor, Jul 30 '14 at 15:47

score 0 · Answer 2 · answered Jul 29 '14 at 20:46

0

This should do it:

REPLACE(@Param, "=", ", ")

answered Jul 29 '14 at 20:46

Metaphor

6,157
10
54
77

Splitting a dynamic string

2 Answers2

Linked