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.
,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.
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.
Change is inevitable... Change for the better is not.
Something like this ...
WHEN CHARINDEX(' ', S1UPSP) > 0 THEN '0' /* S1UPSP Contains spaces */
WHEN LEN(S1UPSP) = 10 THEN RIGHT(S1UPSP, 8) /* S1UPSP = 10 characters long */
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'
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'
END AS AR_LOAD,
The data type of S1UPSP is char.
WHEN LTRIM(S1UPSP) LIKE 'RIOUS%' THEN '0'
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 4 (of 4 total)