Manipulate Records, Inserting character

Comments

7 comments

  • Avatar
    Kobbi Gal

    Hi Chris,

    Can you provide an example of what the data looks like and what is your expected result after manipulation?

     

     

    0
    Comment actions Permalink
  • Avatar
    Christopher Hols

    Hi Kobbi,

     

    sure. My apologies for forgetting to include the examples.

     

    first example: Record is OEIEU, should OE-IEU

    second example: Record is DABVY, should be D-ABVY

     

    Thank you!

    0
    Comment actions Permalink
  • Avatar
    Kobbi Gal

    Hi Chris,

     

    For OEIEU -> OE-IEU use the following:

    concat(concat(Left(record,2), '-'), right(record,3))

     

    For DABVY -> D-ABVY use the following:

    concat(concat(Left(record,1), '-'), substring(record,2))
    0
    Comment actions Permalink
  • Avatar
    Christopher Hols

    Thanks for this, both values are in the same column. How do I solve that?

     

    KR

     

    Chris

    0
    Comment actions Permalink
  • Avatar
    Kobbi Gal

    You will need to use CASE WHEN according to which manipulation you want to apply to the data, i.e.:

    SELECT
    CASE WHEN {some condition to add hyphen after first letter} THEN CONCAT(CONCAT(LEFT(record,1), '-'), SUBSTRING(record,2))
    CASE WHEN {some condition to add hyphen after second letter} THEN CONCAT(CONCAT(LEFT(record,2), '-'), RIGHT(record,3))
    ELSE ....
    FROM ...

     

    0
    Comment actions Permalink
  • Avatar
    Christopher Hols

    I put it like this

     

    Does not work :(

    0
    Comment actions Permalink
  • Avatar
    Michael Becker

    Hi Christopher,

    you actually have to do it a little bit differently. I'm assuming this has to be a single column based on multiple conditions on single column, then you need to do it like this:

    CASE

    WHEN first check THEN do one

    WHEN second check THEN do two

    ELSE do three

    END

    this is the syntax for your result column, so simply copy your lines 2 and 3, remove case in them, put CASE at the beginning, remove FROM line at the end and add END instead, and that should do the trick. Hope it helps.

    Regards,

    Michał

    0
    Comment actions Permalink

Please sign in to leave a comment.