Using CASE on empty string

Question

I have a code that goes like this: ``` SELECT '"35933-14",' || '"' || us_1.gr_UniqueName || '",' || '"' || (CASE WHEN us_1.mls0_PrimaryString = '' THEN 'This is empty' WHEN CAST(Length(us_1.mls0_PrimaryString) AS INT) < 4 THEN ('Less than 4: '|| SUBSTR(us_1.mls0_PrimaryString,1,10000)) ELSE SUBSTR(us_1.mls0_PrimaryString,1,10000) END) || '",' || '"",' || '"",' || '""' FROM us_GroupTab us_1 WHERE (us_1.gr_Active = 1) AND (us_1.gr_PurgeState = 0) AND (us_1.gr_PartitionNumber = 0) AND (us_1.gr_UniqueName IN ('US_HARDWARE_1', 'US_HARDWARE_2','GROUP_NULL')); ``` Basically the problem is that not all empty string is handled, some users are only inputting multiple spaces which the first case statement does not handle. Is there any way to do this, I have tried using TRIM function but it does not work. Thanks!

Accepted Answer

An empty string is the same as null in Oracle, and you can't compare anything to null. You need to use `is null` instead of `= null` or `= ''`.

```
CASE WHEN TRIM(us_1.mls0_PrimaryString) IS null THEN 'This is empty' ...
```

You also don't need to `cast` the length check to `int`. And the maximum length of a `varchar2` before 12c is 4000 chars, so there's no point using 10000 in your `substr`. In fact the first `substr` isn't going to do anything anyway as you already know the length is less than 4.

If you want to remove new lines and carriage returns before checking - and that is perhaps something you should be doing client-side, unless you want to store those too - then you can either replace them first:

```
CASE WHEN TRIM(REPLACE(REPLACE(us_1.mls0_PrimaryString, CHR(10)), CHR(13))) IS null
THEN ...
```

Or more generically remove all whitespace which would catch tabs etc. too:

```
CASE WHEN REGEXP_REPLACE(us_1.mls0_PrimaryString, '[[:space:]]') IS NULL THEN ...
```

Or:

```
CASE WHEN REGEXP_LIKE(us_1.mls0_PrimaryString, '^[[:space:]]*$') THEN ...
```

Note that don't need a separate `trim` with `regexp_replace`.

Using CASE on empty string

Solution

Problem