July 28, 2014 at 3:11 pm
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.
July 29, 2014 at 8:28 am
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.
July 29, 2014 at 9:56 am
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 😕
July 30, 2014 at 1:46 am
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.
July 30, 2014 at 9:57 am
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
August 2, 2014 at 2:29 am
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
August 2, 2014 at 3:42 am
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