Handle space between multivalue parameter values in SSRS

• pwalter83

  SSChampion

  Points: 14575

  More actions

  July 8, 2015 at 8:29 am

  #309679

  Hi,

  I am stuck at a requirement which is how to handle space between multivalue parameter values in SSRS. For e.g. if the values are as follows -'KLO LUG', 'HGY KIU', 'LOT JUY', I know I can use the split function for the commas but its the space between the value which is the problem.

  Can someone please help on this ?

  Thanks.

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  July 8, 2015 at 8:38 am

  #1810980

  pwalter83 (7/8/2015)

  ---

  Hi,

  I am stuck at a requirement which is how to handle space between multivalue parameter values in SSRS. For e.g. if the values are as follows -'KLO LUG', 'HGY KIU', 'LOT JUY', I know I can use the split function for the commas but its the space between the value which is the problem.

  Can someone please help on this ?

  Thanks.

  It's hard to know what your objective is when all you are saying is that a space is causing a problem. We have no idea what these parameters are being used for, nor how they are consumed, so you'll have to explain why you couldn't further use the split function on the space as well. Somehow, I'm not sure I'd want multivalued strings as part of a single parameter that has comma separated values, as that just sounds like a design problem, in that it is unnecessarily complex. Please elaborate in considerably greater detail.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Brandie Tarvin

  SSC Guru

  Points: 173080

  More actions

  July 8, 2015 at 10:11 am

  #1811038

  If the values are solely those between the quotes, just use the splitter and change the comma to a space. Problem solved. If the values include the quotes and the commas, I'm sure you can parse the splitter twice, once for space and once for comma.

  Other than that, as previously mentioned, we would need more detail on the requirements, the same data, the DDL for the table / insert of sample data, and expected results before we could answer this question.

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

• x

  SSC-Insane

  Points: 23660

  More actions

  July 8, 2015 at 10:55 am

  #1811056

  pwalter83 (7/8/2015)

  ---

  Hi,

  I am stuck at a requirement which is how to handle space between multivalue parameter values in SSRS. For e.g. if the values are as follows -'KLO LUG', 'HGY KIU', 'LOT JUY', I know I can use the split function for the commas but its the space between the value which is the problem.

  Can someone please help on this ?

  Thanks.

  I usually join them in SSRS to get them sent to SQL and then split them up there. Once you split them up there, you'll end up with a table looking thing:

  parameters:

  'KLO LUG'

  'HGY KIU'

  'LOT JUY'

  Now to get rid of the leading spaces, I'd probably use ltrim on it.