June 12, 2021 at 2:03 pm
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
June 12, 2021 at 3:05 pm
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
June 12, 2021 at 4:25 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 14, 2021 at 12:54 am
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 modifiedportionkeywhat 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
Change is inevitable... Change for the better is not.
June 14, 2021 at 4:21 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 14, 2021 at 7:37 pm
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
Change is inevitable... Change for the better is not.
June 14, 2021 at 8:16 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply