Ssrs expression substring from string to char "-"

Question

0.00/5 (No votes)

See more:

SSRS-2008

SSRS-2005

how can i sub string from field for example i have this in
my field "Hyper super market - Saudi Arabia"
i need all words before "-" this what i need "Hyper super market"
how can i remove all char after "-"

What I have tried:

i'v try also this and not working will

C#

=mid(Fields!Description.Value,0,instr(Fields!Description.Value,"-")+1)

Posted 17-Feb-20 22:10pm

mohammed mqi

Updated 17-Feb-20 22:28pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2020-02-17T22:29:00

Solution 1

Have you tried to use Left function, instead of Mid?
=Left(Fields!Description.Value, InStr(Fields!Description.Value, "-")-1)

More at: Expression Examples (Report Builder) - SQL Server Reporting Services (SSRS) | Microsoft Docs[^]

[EDIT]

Richard Deeming wrote (in the comment):
The IIF function evaluates both branches in all cases, which is why you're getting an error.

You'll need to use IIF in a slightly different position:

=Left(Fields!Description.Value, IIF(InStr(Fields!Description.Value, "-") > 0, InStr(Fields!Description.Value, "-") - 1, Len(Fields!Description.Value)))

[/EDIT]

Posted 17-Feb-20 22:29pm

Maciej Los

Updated 19-Feb-20 7:27am

v4

Comments

mohammed mqi 18-Feb-20 4:32am

it's give me #Error

Maciej Los 18-Feb-20 5:43am

Sorry, my bad. Updated (removed 1 from Instr function).

mohammed mqi 18-Feb-20 8:26am

thanks it works fine but i have an issue if there's no dash"-" in my word it's give me error how can i avoid that?
also i try this and and also show me error for word doesn't contain dash

=IIf(InStr(Fields!Description.Value, "-") > 0,Left(Fields!Description.Value, InStr(Fields!Description.Value, "-")-1),Fields!Description.Value)

Maciej Los 18-Feb-20 8:57am

Seems, you have to inspect your data. Sorry, but i have not an access to them.

Richard Deeming 18-Feb-20 15:06pm

The IIF function evaluates both branches in all cases, which is why you're getting an error.

You'll need to use IIF in a slightly different position:

=Left(Fields!Description.Value, IIF(InStr(Fields!Description.Value, "-") > 0, InStr(Fields!Description.Value, "-") - 1, Len(Fields!Description.Value)))

mohammed mqi 19-Feb-20 2:31am

thanks Richard Deeming it's work perfectly that's great
also thanks Maciej Los

Maciej Los 19-Feb-20 2:43am

Sounds like an answer...

Richard Deeming 19-Feb-20 6:51am

It's only a minor tweak to yours. :)

Maciej Los 19-Feb-20 13:33pm

Thanks, but i really think that your comment is worth to post as an answer....
So, i've updated my answer... and i've used your comment as a part of answer.
Any way...
Thank you, Richard.

mohammed mqi 20-Feb-20 1:15am

thanks for editing your answer