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

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

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 4 (of 4 total)