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) -
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.
-
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