Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Parsing a String in SSRS Expand / Collapse
Author
Message
Posted Monday, July 28, 2014 3:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 9:02 AM
Points: 5, Visits: 24
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.
Post #1597080
Posted Tuesday, July 29, 2014 8:28 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:42 AM
Points: 460, Visits: 859
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.
Post #1597321
Posted Tuesday, July 29, 2014 9:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 9:02 AM
Points: 5, Visits: 24
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 :?
Post #1597376
Posted Wednesday, July 30, 2014 1:46 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:42 AM
Points: 460, Visits: 859
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.
Post #1597606
Posted Wednesday, July 30, 2014 9:57 AM This worked for the OP Answer marked as solution
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 9:02 AM
Points: 5, Visits: 24
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
Post #1597830
Posted Saturday, August 2, 2014 2:29 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 18, 2014 5:11 AM
Points: 23, Visits: 51
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 + 1 The 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.
Post #1598858
Posted Saturday, August 2, 2014 3:42 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:42 AM
Points: 460, Visits: 859
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 + 1 The 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.
Post #1598883
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse