Parsing a String in SSRS

  • What up Guys,

    First time posting a question here. I'm not entirely sure how to solve. I have the following string I need to parse in SSRS so that it looks like the below. the string can change to have more or less values but the pattern will be the same. I have tried using the split function, but I can only get it to return one value

    What I Tried so far

    =Fields!Tix_Price.Value.ToString().Split("==")(0) but this only returns one value.

    I've also tried directly in SP with the charindex function but I get the same results. Can't get it to display just "Child, Adult"

    String: CHILD=1625=0n=1==3&ADULT=1900=1j=1==1

    What it should look like: CHILD, ADULT

    Any help would be appreciated.

  • dabello88 (7/28/2014)


    What up Guys,

    First time posting a question here. I'm not entirely sure how to solve. I have the following string I need to parse in SSRS so that it looks like the below. the string can change to have more or less values but the pattern will be the same. I have tried using the split function, but I can only get it to return one value

    What I Tried so far

    =Fields!Tix_Price.Value.ToString().Split("==")(0) but this only returns one value.

    I've also tried directly in SP with the charindex function but I get the same results. Can't get it to display just "Child, Adult"

    String: CHILD=1625=0n=1==3&ADULT=1900=1j=1==1

    What it should look like: CHILD, ADULT

    Any help would be appreciated.

    A field in SSRS, or text box, contains a single value. The split function creates an array, and it looks like you are just returning the first value of the array (0) because the array is zero indexed. You are also splitting on the double equals sign "==" which I am guessing returns this whole nasty thing "CHILD=1625=0n=1".

    Try splitting on a single equals. You would then want the zero'th and fifth item in the array, and you want to throw in a comma it seems as well.

    Sorry not at a workstation with ssrs right now but assuming your syntax above is right it might be something like:

    =Fields!Tix_Price.Value.ToString().Split("==")(0) + "," + Fields!Tix_Price.Value.ToString().Split("==")(5)

    Is plus the concatenation operator? I'll have to double check in the morning. You might also want to split that second string again by the & ampersand and take the second (1) item.

    You might want to take a Google at Split Join as well, which is common syntax for splitting into an array, then joining the array elements back together with a delimited, often a comma as you've indicated you want.

  • thanks for the reply!

    I google split function and found the following function that gets me on my way but I'm having trouble trimming it so I only get the values I need. I'm also having trouble removing the "&". Note that this string is dynamic so it can be "CHILD=1625=0n=1==3&ADULT=1900=1j=1==1" or "ADULT=1075=0r=12==4" or ADULT=1100=2J=4==1&SENIOR=1000=2K=4==1", etc.

    I'm wondering if I need an IIF staments to account for the "&"

    =Join(Split(Fields!Tix_Price.Value,"="),", ")

    result: CHILD, 900, A1, 3, , 1&SENIOR, 900, A4, 3, , 2

    would like it to look like CHILD, SENIOR

    Still not sure how to remove the extra values and only keep the two I need 😕

  • dabello88 (7/29/2014)


    thanks for the reply!

    I google split function and found the following function that gets me on my way but I'm having trouble trimming it so I only get the values I need. I'm also having trouble removing the "&". Note that this string is dynamic so it can be "CHILD=1625=0n=1==3&ADULT=1900=1j=1==1" or "ADULT=1075=0r=12==4" or ADULT=1100=2J=4==1&SENIOR=1000=2K=4==1", etc.

    I'm wondering if I need an IIF staments to account for the "&"

    =Join(Split(Fields!Tix_Price.Value,"="),", ")

    result: CHILD, 900, A1, 3, , 1&SENIOR, 900, A4, 3, , 2

    would like it to look like CHILD, SENIOR

    Still not sure how to remove the extra values and only keep the two I need 😕

    The *right* way to do this is to use REPLACE() function with a REGEX pattern, but here's something a bit more agricultural:

    =(

    SWITCH(INSTR(Parameters!INPUT.Value,"CHILD") >= 1, ",CHILD") +

    SWITCH(INSTR(Parameters!INPUT.Value,"ADULT") >= 1, ",ADULT") +

    SWITCH(INSTR(Parameters!INPUT.Value,"SENIOR") >= 1, ",SENIOR")

    ).Remove(0,1)

    It will only work for values of ADULT or CHILD or SENIOR, but you can easily add more lines to it if there are other options. It's also probably not great to have "business logic" like this in the report, but it should work. I am testing on SQL Server 2012, so not sure if the Remove() function will work for you. If not you can do something ugly like use the RIGHT() function, passing in the LEN() of that whole string minus 1. If you have trouble with that I can post syntax.

  • Thanks for the reply. hard coding the values is not something that I want to do as this values can change without notice. As I continued to scavange the internet, Gouri Shankar Aechoor provided the solution I needed. He created a function that splits the string, id the string and then I can call which string i need in the function.

    check this out

    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

  • So this can easily be done in SQL.

    But since this is string parsing it will be intense on the sql server. So i recommend to rather change the way the data are received.

    But if you want to attempt in doing this in sql

    here you go:

    declare @String varchar(50) = 'CHILD=1625=0n=1==3&ADULT=1900=1j=1==1'

    /*

    -- Get the text left of the first =

    select left(@String,charindex('=',@String,1)-1)

    --get the first index of the &

    select charindex('&',@String)

    -- get the first index of the first = after the first &

    select charindex('=',@String,charindex('&',@String))

    -- Get the text between the first & and only look at the amount of char to return.

    -- First & index + 1The amount of char to return

    select SUBSTRING(@String,charindex('&',@String)+1, charindex('=',@String,charindex('&',@String))-charindex('&',@String)-1)

    */

    select left(@String,charindex('=',@String,1)-1)+', '+ SUBSTRING(@String,charindex('&',@String)+1,charindex('=',@String,charindex('&',@String))-charindex('&',@String)-1)

    I have added some small select statements so you can see what each step is trying to do.

    Hope this helps

    --------------------------------------------------------------------------------------------------------------------------------------------------------
    To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
    http://www.sql-sa.co.za

  • Daniel Matthee (8/2/2014)


    So this can easily be done in SQL.

    But since this is string parsing it will be intense on the sql server. So i recommend to rather change the way the data are received.

    But if you want to attempt in doing this in sql

    here you go:

    declare @String varchar(50) = 'CHILD=1625=0n=1==3&ADULT=1900=1j=1==1'

    /*

    -- Get the text left of the first =

    select left(@String,charindex('=',@String,1)-1)

    --get the first index of the &

    select charindex('&',@String)

    -- get the first index of the first = after the first &

    select charindex('=',@String,charindex('&',@String))

    -- Get the text between the first & and only look at the amount of char to return.

    -- First & index + 1The amount of char to return

    select SUBSTRING(@String,charindex('&',@String)+1, charindex('=',@String,charindex('&',@String))-charindex('&',@String)-1)

    */

    select left(@String,charindex('=',@String,1)-1)+', '+ SUBSTRING(@String,charindex('&',@String)+1,charindex('=',@String,charindex('&',@String))-charindex('&',@String)-1)

    I have added some small select statements so you can see what each step is trying to do.

    Hope this helps

    This is much better than the function in the previous comment. That function is a multi table valued function, which are almost as bad as UDFs. The optimiser will treat it like a black box.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply