what condition case when related to portion key mean ?

  • I work on sql server 2012 I don't understand condition below do

    this condition as below

    case WHEN Po.PortionKey LIKE '%[_]%' 
    THEN
    SUBSTRING(Po.PortionKey, LEN(LTRIM(RTRIM(Po.PortionKey)))+1,LEN(LTRIM(RTRIM(Po.PortionKey)))) end
    as modifiedportionkey

    what condition above mean

    can please give me sample

  • Have a look at the Docs for SUBSTRING

    SUBSTRING ( expression ,start , length )

    When the CASE statement is evaluated as TRUE, because you're adding +1 to the length LEN of the input expression, it will return no  characters.  If the CASE is evaluated as FALSE, because there is no ELSE condition provided, it will return NULL

    select case WHEN Po.PortionKey LIKE '%[_]%' 
    THEN SUBSTRING(Po.PortionKey,
    LEN(LTRIM(RTRIM(Po.PortionKey)))+1,
    LEN(LTRIM(RTRIM(Po.PortionKey)))) end as modifiedportionkey,
    case WHEN Po.PortionKey LIKE '%[_]%'
    THEN 'True' else 'False' end as IsTrueCondition
    from (values ('blah [_] blah'),
    ('blah blah blah')) Po(PortionKey);
    modifiedportionkeyIsTrueCondition
    True
    NULLFalse

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Well - this code is going to do something a bit different than what Steve outlined, depending on the actual data in the PortionKey column.  If there are leading blanks - this code will return the last nn characters where nn is the number of leading blanks.

    select case WHEN Po.PortionKey LIKE '%[_]%' 
    THEN SUBSTRING(Po.PortionKey,
    LEN(LTRIM(RTRIM(Po.PortionKey)))+1,
    LEN(LTRIM(RTRIM(Po.PortionKey)))) end as modifiedportionkey,
    case WHEN Po.PortionKey LIKE '%[_]%'
    THEN 'True' else 'False' end as IsTrueCondition,
    from (values (' blah [_] blah '),
    (' blah blah blah ')) Po(PortionKey);

    So - with one leading blank the value returned will be 'h'.  The last character in the string prior to the trailing blanks which are removed for the substring.  If there are 2 leading blanks the value returned would be 'ah'.

    To understand why the code was written this way - you need to evaluate the data in the PortionKey and the value being returned from this statement in the query where it is utilized.  For example, is the underscore always the last character in the PortionKey - or can that character be anywhere in the string?

    How is the returned column utilized by the caller?  That will also help determine why the code is built this way - which can then lead to options to optimize the code (if needed).

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • ahmed_elbarbary.2010 wrote:

    I work on sql server 2012 I don't understand condition below do this condition as below

    case WHEN Po.PortionKey LIKE '%[_]%' 
    THEN
    SUBSTRING(Po.PortionKey, LEN(LTRIM(RTRIM(Po.PortionKey)))+1,LEN(LTRIM(RTRIM(Po.PortionKey)))) end
    as modifiedportionkey

    what condition above mean can please give me sample

    As it's written, it returns either a NULL or and empty string.  I think someone forgot to complete the 2nd operand of the substring, which should probably equate to the position of the underscore + 1.  In other words, I think it was meant to be a string splitter that would use the right hand "word" if an underscore was used as a delimiter.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Jeff Moden wrote:

    As it's written, it returns either a NULL or and empty string.  I think someone forgot to complete the 2nd operand of the substring, which should probably equate to the position of the underscore + 1.  In other words, I think it was meant to be a string splitter that would use the right hand "word" if an underscore was used as a delimiter.

    Not quite true - it depends on the data and whether that data has leading spaces.  Trailing spaces are ignored - but leading spaces will adjust what is returned.  As I stated - the only way to determine the purpose of this code is to perform the analysis on where it is used and what is the expected return value in that code.  And - what does the application expect in that column?

    We can guess - but since we don't have any exposure to how this is used that is all we can do.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    Jeff Moden wrote:

    As it's written, it returns either a NULL or and empty string.  I think someone forgot to complete the 2nd operand of the substring, which should probably equate to the position of the underscore + 1.  In other words, I think it was meant to be a string splitter that would use the right hand "word" if an underscore was used as a delimiter.

    Not quite true - it depends on the data and whether that data has leading spaces.  Trailing spaces are ignored - but leading spaces will adjust what is returned.  As I stated - the only way to determine the purpose of this code is to perform the analysis on where it is used and what is the expected return value in that code.  And - what does the application expect in that column?

    We can guess - but since we don't have any exposure to how this is used that is all we can do.

    So it does.  I works kind of light a "RIGHT" in that it appears to return the right N number of characters where N is defined as the number of leading spaces and it's only activated if there's an underscore in the string and yet the position of the underscore isn't used numerically to drive the return.  Of course, things go pretty wonky if the underscore appears in the last N characters of the non-trailing space characters especially if the are spaces prior to the underscore and the underscore is the last non-space character.  Someone needed something really esoteric.

    Shifting gears a bit, this is one of the many reasons why I'll make a call-to-arms when people say things like "Comments are not required... just read the code".  Yep... we can figure out WHAT it does but WHY someone would want to do this is anyone's guess.

     

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Jeff Moden wrote:

    Shifting gears a bit, this is one of the many reasons why I'll make a call-to-arms when people say things like "Comments are not required... just read the code".  Yep... we can figure out WHAT it does but WHY someone would want to do this is anyone's guess. 

    There are certainly times when the why is more important than the how or what.  In a lot of cases - the code can be written to be self-documenting and it will be obvious (or should be) to any future developer reviewing the code as to what/how and why the code was written that way.

    But - any time there is the slightest possibility that the code itself won't answer that question, a simple statement of what and why that code segment is doing could save countless man-hours of time.  And all too frequently that time is the original developers (my) time saved since I wrote that code many, many, many years ago 🙂

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

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

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