Removing Number from String in SSRS

Question

Post reply

Removing Number from String in SSRS

ssrsnewbie22

SSC Enthusiast

Points: 174
More actions
September 20, 2022 at 11:30 am

#4089746

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.

Viewing 10 posts - 1 through 9 (of 9 total)

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

MarkP Old Hand Points: 305 More actions · Answer 1

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. 🙂

below86 SSChampion Points: 11532 More actions · Answer 2

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.

ssrsnewbie22 SSC Enthusiast Points: 174 More actions · Answer 3

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.

MarkP Old Hand Points: 305 More actions · Answer 4

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.

below86 SSChampion Points: 11532 More actions · Answer 5

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.

below86 SSChampion Points: 11532 More actions · Answer 6

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.

below86 SSChampion Points: 11532 More actions · Answer 7

This will get rid of any amount of numbers you have:

DECLARE @var VARCHAR(200);

DECLARE @expres VARCHAR(50) = '%[0-9]%';

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

WHILE PATINDEX( @expres, @var ) > 0

SET @var = REPLACE(Replace(REPLACE( @var, SUBSTRING( @var, PATINDEX( @expres, @var ), 1 ),''),'-',' '), '()', '')

SELECT @var;

This returns: Apple (red)(green)

A simple google got me most of this.

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

ssrsnewbie22 SSC Enthusiast Points: 174 More actions · Answer 8

Thanks you for your insight below86. I believe I fail to mention I am using a Store Procedure and can not freely write a Text SQL in the report. I have search the web for ideas and have ran into what you have suggested, but since I am constraint to what I can do in Expression or Custom Code, I could not use that method. However, good news is I discovered you can write .Net language in SSRS. So by using the System Text RegExpression and Replace function I can add in the "wildcard" of .Net and it works. Here is a link to .Net Quick Reference:

https://learn.microsoft.com/en-us/dotnet/standard/base-types/regular-expression-language-quick-reference

I found using this method you can replace any pattern numbers and character at the same time. Again, Thanks for all your feedback!

below86 SSChampion Points: 11532 More actions · Answer 9

You could have taken the code and created a SQL Stored Procedure(SP) you could call to replace the values in the field you sent the SP. Then you could use it on other columns as needed and the code is stored in one place if you need to make changes.

But, what ever you are more comfortable with, I know I wouldn't want .NET coding in our SSRS reports.

This reply was modified 2 years, 1 month ago by below86.

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