Hello
I have an output in my queries that gives me:
xxxxxxx xxxxxxxxx : 123456 (xx) - xxxxxxx...
or
xxxxxxx xxxxxxxxx : 123456789 (xx) - xxxxxxx...
basically text before either a 6 or 9 digit number then text after
I've created a column in a table in SSRS that just shows the number, and I used this expression:
=Mid(Fields!my.Value,InStr(Fields!my.Value,":")+1,Instr(InStr(Fields!my.Value,":")+3,Fields!my.Value," ") - InStr(Fields!my.Value,":") -1)
Which now gives me an output of 123456 or 123456789.
However I'd like to do an additional thing. I'd like to create an output that if the digit is 6 numbers long returns London and if the digit is 9 numbers long then it returns Paris . Is this possible - I've tried with IIF but have singularly failed so far. I tried this:
=IIF(Len(Mid(Fields!my.Value,InStr(Fields!my.Value,":")+1,Instr(InStr(Fields!my.Value,":")+3,Fields!my.Value," ") - InStr(Fields!my.Value,":") -1))=9,"Paris",IIF(Len(Mid(Fields!my.Value,InStr(Fields!my.Value,":")+1,Instr(InStr(Fields!my.Value,":")+3,Fields!my.Value," ") - InStr(Fields!my.Value,":") -1))=6,"London","Wrong Number"))
August 20, 2019 at 11:48 am
Why not just look for the dash, "-"?
IIF(InStr(Fields!my.Value,"-")=36, "Paris", "London")
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 20, 2019 at 11:55 am
Thanks for getting back but sorry you've lost me. Are you suggesting I replace ":" with "-" ? Apologies. Thanks.
August 20, 2019 at 12:03 pm
In this string the dash appears in the 33rd (or 32 starting from 0) position:
xxxxxxx xxxxxxxxx : 123456 (xx) - xxxxxxx...
In this string the dash appears in the 36th position:
xxxxxxx xxxxxxxxx : 123456789 (xx) - xxxxxxx...
therefore:
IIF(InStr(Fields!my.Value,"-")=36, "Paris", "London")
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 20, 2019 at 1:20 pm
I see, thanks. No sadly the : appears in exactly the same place in both instances so the neat solution offered above - for which many thanks - doesn't work unfortunately/
August 20, 2019 at 1:58 pm
I have managed to solve all this with a CASE statement rather than an expression in SSRS, so no major worries about this unless someone has a solution to my original question - would be interested but this is very low priority! Thanks to those who got back to me.
August 20, 2019 at 2:02 pm
If this can't be done by absolute offset then that raises a bunch of issues. What can you say about the characters that appear in the 'xxxxxxx xxxxxxx' segments? Can you guarantee there are no colons no dashes? Between the colon and the dash can you guarantee there are 2 and only 2 different layout patterns, one with 6 digits and one with 9 digits? This is query output you're re-querying? If so, it's could be much better to post the original query and we could fix that up. Fwiw, my post should've used the mid function to base the selection off of absolute position.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 20, 2019 at 2:10 pm
Thanks for getting back. In terms of the text that appears: it is constant and always the same, basically:
Working Reference : [then the digits] (text in brackets) - loads more text
And it's either 6 or 9 digits that appear. Always. Very rarely the 9 digit sequence has TEST before it.
August 20, 2019 at 3:01 pm
Just to be clear there are 3 possible patterns?
In case 1, the open parenthesis will always appears in 30th position?
In case 2, the open parenthesis will always appears in 33rd position?
In case 3, the open parenthesis will always appears in 38th position?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 20, 2019 at 3:27 pm
Yes, that's right. Thank you.
IIF(Mid(Fields!my.Value, 30, 1)="(","London", "Paris")
or (because the position counts counted the brackets):
IIF(Mid(Fields!my.Value, 28, 1)="(","London", "Paris")
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 21, 2019 at 7:10 am
Thanks - option 2 worked. Thank you for taking the time to help me and for your patience. Much appreciated.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply