Removing Number from String in SSRS

  • Hello,

    New to SSRS 2016 here, and appreciate any help.  Field value coming in with "( " in front of a number, and I was able to remove it using Mid function, but now there are other values where there are values where it is the 2nd "(" that enclose the number.  For example

    Field value "Apple (23) "  Mid(Fields!Field.Value, 1, InStr(Fields!Field.Value, "(") - 1)  will return "Apple"

    But now:

    Field Value "Apple (red) (23)" and the function above will return "Apple" again, but I need it to be "Apple (red)"

    Is there a function that I can search for the 1st "number" and remove it?  Or to remove the 2nd "("?

    Thanks in advance.

  • Will it always be that format:

    Apple (red) (23)

    Apple (23)

    Grape (green) (12)

    Grape (12)

    And so on?

    If so, you could use a CASE expression to count the number of opening brackets:

    CASE WHEN LEN(Fields!Field.Value) - LEN(REPLACE(Fields!Field.Value, '(', '')) = 1 THEN Mid(Fields!Field.Value, 1, InStr(Fields!Field.Value, "(") - 1) ELSE ...... END where ...... represents your formula to strip out to get Apple (red)

    For that, you could use reverse, chop down to the first occurrence of ")" then reverse back and chop 2 off the length of the string.

    Without knowing your exact string options, I've at least given you a starter for ten. 🙂

  • If MarkP is correct on the format, where you want to remove the last 4 characters, why not use this:

    SUBSTRING(@var, 1, LEN(@var) - 4)

    Replace the @var with your field value.

     

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • Thank you MarkP and SSChampion.  I was thinking something in the same line as yours, and incorporated both ideas and came up with this:

    Mid(Fields!Field.Value, 1, Len(Fields!Field.Value) - InStr(StrReverse(Fields!Field.Value), "("))

    At first I thought the data would be static, but I was wrong since it came in as "Apple (Red) (23)" vs prior data "Apple (23)".   There is no guarantee that the data would change again, and next time it could in as "Apple (Red) (23) (Granny)", or something like that.  So I am still looking for a way to identify the 1st number within a string.  However, the current solution worked for me.   Thanks again.

  • Good to hear you've got a working solution. Communication is the key now - make sure you're told in advance if the format will change again so you've got time to prepare a new piece of code.

  • So what will you store if you have "Apple (Red) (23) (Granny)"?  "Apple (Red) (Granny)"?

    What is the purpose to remove the number?  Can the number be more than 2 digits?

    You can use the PATINDEX to find the first number:

    SELECT position = PATINDEX('%[0-9]%', 'Please ensure that 1 door is locked!');

    This returns 20.

    https://learn.microsoft.com/en-us/sql/t-sql/functions/patindex-transact-sql?view=sql-server-ver16

     

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • All of your examples also only show (23) as what you want to remove, it you know the string then a replace would be the easiest way to remove that string.

    DECLARE @var VARCHAR(200);

    SET @var = 'Apple (red)(23)(green)';

    SELECT REPLACE(@var, '(23)', '')

    This would return Apple (red)(green).

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

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

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