asked on

SQL Query

I'm trying to understand this piece of code. The part that is confusing me is the Else Case. I understand that the Substring function extract data from an expression, and the CharIndex returns a number, which i think represents the length of characters that the Substring function will output.

1. What is the -1 doing to the Charindex function.
2. What does the code do to this value in the table. What would the 90A- become after the code has been executed. And why?:

RSPF_PROD_N
90A-

SELECT DISTINCT
CASE
WHEN CHARINDEX('-',RSPF.RSPF_PROD_N) = 0 THEN LTRIM(RTRIM(RSPF.RSPF_PROD_N))
ELSE CASE
WHEN RSPF.RSPF_R_OR_S = 'R' THEN SUBSTRING(LTRIM(RTRIM(RSPF.RSPF_PROD_N)), 1, CHARINDEX('-',LTRIM(RTRIM(RSPF.RSPF_PROD_N)))-1)
ELSE LTRIM(RTRIM(RSPF.RSPF_PROD_N))
END
END AS PRODUCT_NUM

UnifiedIS

CHARINDEx represents the position of a character, the -1 moves the value back one spot to capture everything up to but not including the found value.
The first condition would fail (CHARINDEX('-'...) does not = 0) so "90A-" would fall to the second case statement.
The value of RSPF.RSPF_R_OR_S determines what is returned. What is that value?

Jim Horn

>CHARINDEX('-',LTRIM(RTRIM(RSPF.RSPF_PROD_N)))-1)
LTRIM(RTRIM(x)) means remove any leading and trailing spaces from x
CHARINDEX('-', x) means search x for the first instance of '-', and return that character position

For example, the below code returns 4, as the dash is the 4th character from the start

SELECT CHARINDEX('-', '123-456')

Open in new window

So -1 would be 3.

ASKER CERTIFIED SOLUTION

UnifiedIS

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial