CASE statement with multiple conditions (trimming & detecting spaces in between)

  • Hello everyone,

    I am trying to create CASE statement that will do three different things at the time. The column S1UPSP has various issues that I am trying to achieve.

    1)  The column  S1UPSP sometimes has spaces, it is almost always (maybe even always) third from the left.

    An example:

    ,CASE WHEN  S1UPSP='618179 30 ' THEN '0'

    I need to write a statement that will make it '0' if such anomaly happens.

    2) If the field has more than 8 characters (sometimes we have 10 of characters) it should trim the first two numbers and it 8 characters.

    WHEN S1UPSP='8074278015' THEN '74278015'

    I will appreciate any ideas.

    Thank you.

  • On the first item, I'm not grokking what the anomaly is that you speak of.

    On the second, lookup the LEN() function and the SUBSTRING function.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Something like this ...

    CASE
    WHEN CHARINDEX(' ', S1UPSP) > 0 THEN '0' /* S1UPSP Contains spaces */
    WHEN LEN(S1UPSP) = 10 THEN RIGHT(S1UPSP, 8) /* S1UPSP = 10 characters long */
    END
  • Thank you.

    I think it works. Thank you. One more issue though.

    I am not sure why but this part doesn't work  WHEN S1UPSP LIKE 'RIOUS%' THEN '0'

    Sometimes I have RIOUS932 or some other RIOUS% and I need to make them '0'. . What's wrong with this?  WHEN S1UPSP LIKE 'RIOUS%' THEN '0'

     

    ,CASE

    WHEN LEFT(LTRIM(RTRIM(S1UPSP)),5) LIKE'%RIOUS%' THEN '0'

    WHEN CHARINDEX(char(32), LTRIM(RTRIM(S1UPSP)), 1)>0 THEN '0'

    WHEN LEN(S1UPSP)>8 THEN RIGHT(S1UPSP, 8)

    WHEN S1UPSP = '' THEN '0'

    ELSE S1UPSP

    END AS AR_LOAD,

    The data type of S1UPSP is char.

    Issue2

  • Try this

    WHEN LTRIM(S1UPSP) LIKE 'RIOUS%' THEN '0'

     

    or this

    WHEN PATINDEX( '%RIOUS%', S1UPSP) > 0 THEN '0'

    Is it possible that leading "spaces" are not char(32), but some other non-printing character.

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

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