locked
replace last delimeter with "and" in a string in SSRS RRS feed

  • Question

  • Hi,
    I want to
    replace last delimeter (comma) with and  in a string in SSRS 2008 R2.
    if there are more than one unit, and the Area of the units are different, then the area of each must must be listed separated by commas and ‘and’ before the last one.

    i.e.,
    Input string- Hi,Hello,HRU,

    Desired output- Hi,Hello and  HRU..

    how can I achieve this (and before last word) ?

    (suggest in SSRS or even in SQL)

    Rgds/-


    • Edited by mafab Friday, March 7, 2014 7:49 PM
    Friday, March 7, 2014 7:48 PM

Answers

  • Your example seems to indicate you wish to substitute "and" for the second to last delimiter and ".." for the last. Is that the ask? Will there actually be a trailing delimiter on the input string?

    This can be done several ways: In the dataset, in an expression, using custom code.

    The approach for the dataset and expression are similar, just the language is different. Dataset will usually deliver better performance since the source is often a more powerful server (in the case of a SQL dataset).

    In an SSRS expression it might look like this:

    =Left(Fields!InputString.Value,InStrRev(Fields!InputString.Value,",")-1)+" and "+Right(Fields!InputString.Value,Len(Fields!InputString.Value)-InStrRev(Fields!InputString.Value,","))

    This assumes the input string is actually a field from your dataset but it could be anything. The Left piece gets the string that occurs before the last comma without the comma, then append " and ", then the Right expression gets everything after the last comma and appends it. If you will have a trailing delimiter, you could substitute the following formula anywhere you see Fields!InputString.Value:

    =Replace(Trim(Replace(Fields!InputString.Value,","," "))," ",",")

    Because Trim in an SSRS expression only trims leading and trailing spaces, the delimiters must be converted to a space. So this only works if there are no spaces in the original string. If the input is "Hi,Hello,How are you," the result of this formula will be "Hi,Hello,How,are,you" because the spaces between How, are and you will get replaced by ",". To handle embedded spaces makes the expression more complex. Assuming the input string is "Hi,Hello,How are you,":

    =Replace(Replace(Trim(Replace(Replace(Fields!InputString.Value," ","|"),","," "))," ",","),"|"," ")

    This expression uses 2 more replace statements. The innermost statement substitues "|" for the embedded spaces so they don't get replace with ",". You should use a character that you are sure will not appear in the string otherwise. Or use a sequence of characters like "|^|" that you are sure won't appear if any one character may possibly appear. The outermost Replace restores the "|" characters back to spaces.

    A code solution may be easier. Just wrap the below in a simple function:

    return str.Trim(",".ToCharArray()).Substring(0, str.Trim(",".ToCharArray()).LastIndexOf(",") - 1) + " and " + str.Trim(",".ToCharArray()).Substring(str.Trim(",".ToCharArray()).LastIndexOf(",") + 1);

    This is the VB.Net equivalent of the expressions above.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    • Marked as answer by Alisa Tang Friday, March 14, 2014 1:06 AM
    Friday, March 7, 2014 11:11 PM
  • Hi,

    SQL

    DECLARE @String NVARCHAR(50) = 'Hi,Hello,HRU'
    SELECT REVERSE(STUFF(REVERSE(@String),CHARINDEX(',',REVERSE(@String)),1,' dna ')) Units

    SSRS Expression:

    =Mid("Hi,Hello,HRU",1,InStrRev("Hi,Hello,HRU" , ",")-1) + " and " 
    + Mid("Hi,Hello,HRU",InStrRev("Hi,Hello,HRU" , ",")+1,LEN("Hi,Hello,HRU") )


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    • Proposed as answer by Alisa Tang Monday, March 10, 2014 5:09 AM
    • Marked as answer by Alisa Tang Friday, March 14, 2014 1:06 AM
    Saturday, March 8, 2014 4:15 AM

All replies

  • Your example seems to indicate you wish to substitute "and" for the second to last delimiter and ".." for the last. Is that the ask? Will there actually be a trailing delimiter on the input string?

    This can be done several ways: In the dataset, in an expression, using custom code.

    The approach for the dataset and expression are similar, just the language is different. Dataset will usually deliver better performance since the source is often a more powerful server (in the case of a SQL dataset).

    In an SSRS expression it might look like this:

    =Left(Fields!InputString.Value,InStrRev(Fields!InputString.Value,",")-1)+" and "+Right(Fields!InputString.Value,Len(Fields!InputString.Value)-InStrRev(Fields!InputString.Value,","))

    This assumes the input string is actually a field from your dataset but it could be anything. The Left piece gets the string that occurs before the last comma without the comma, then append " and ", then the Right expression gets everything after the last comma and appends it. If you will have a trailing delimiter, you could substitute the following formula anywhere you see Fields!InputString.Value:

    =Replace(Trim(Replace(Fields!InputString.Value,","," "))," ",",")

    Because Trim in an SSRS expression only trims leading and trailing spaces, the delimiters must be converted to a space. So this only works if there are no spaces in the original string. If the input is "Hi,Hello,How are you," the result of this formula will be "Hi,Hello,How,are,you" because the spaces between How, are and you will get replaced by ",". To handle embedded spaces makes the expression more complex. Assuming the input string is "Hi,Hello,How are you,":

    =Replace(Replace(Trim(Replace(Replace(Fields!InputString.Value," ","|"),","," "))," ",","),"|"," ")

    This expression uses 2 more replace statements. The innermost statement substitues "|" for the embedded spaces so they don't get replace with ",". You should use a character that you are sure will not appear in the string otherwise. Or use a sequence of characters like "|^|" that you are sure won't appear if any one character may possibly appear. The outermost Replace restores the "|" characters back to spaces.

    A code solution may be easier. Just wrap the below in a simple function:

    return str.Trim(",".ToCharArray()).Substring(0, str.Trim(",".ToCharArray()).LastIndexOf(",") - 1) + " and " + str.Trim(",".ToCharArray()).Substring(str.Trim(",".ToCharArray()).LastIndexOf(",") + 1);

    This is the VB.Net equivalent of the expressions above.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    • Marked as answer by Alisa Tang Friday, March 14, 2014 1:06 AM
    Friday, March 7, 2014 11:11 PM
  • Hi,

    SQL

    DECLARE @String NVARCHAR(50) = 'Hi,Hello,HRU'
    SELECT REVERSE(STUFF(REVERSE(@String),CHARINDEX(',',REVERSE(@String)),1,' dna ')) Units

    SSRS Expression:

    =Mid("Hi,Hello,HRU",1,InStrRev("Hi,Hello,HRU" , ",")-1) + " and " 
    + Mid("Hi,Hello,HRU",InStrRev("Hi,Hello,HRU" , ",")+1,LEN("Hi,Hello,HRU") )


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    • Proposed as answer by Alisa Tang Monday, March 10, 2014 5:09 AM
    • Marked as answer by Alisa Tang Friday, March 14, 2014 1:06 AM
    Saturday, March 8, 2014 4:15 AM
  • Very good response Tim.

    I wish all users voted for the collaborators that answered their questions. Especially when the answers are as detailed and useful as yours.

    Thanks for taking the time to explain it so well.

    All the best,

    Virgil


    There is always something above the sky

    Tuesday, November 28, 2017 5:30 PM