Trimming then adding text

  • 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"))

    • but the output I got was 'wrong number' - so close but no cigar. Would massively appreciate any final tweak suggestions to get this to work. Thanks
  • 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

  • Thanks for getting back but sorry you've lost me. Are you suggesting I replace ":" with "-" ? Apologies. Thanks.

  • 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

  • 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/

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

  • 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

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

  • Just to be clear there are 3 possible patterns?

    1. Working Reference : [123456] (text in brackets) -  loads more text
    2. Working Reference : [123456789] (text in brackets) -  loads more text
    3. Working Reference : TEST [123456789] (text in brackets) -  loads more text

    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

  • 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

  • Thanks - option 2 worked. Thank you for taking the time to help me and for your patience. Much appreciated.

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

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