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!

Alex Poole · Accepted Answer · 2014-07-09 13:47:31Z

5

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.

edited Jul 9, 2014 at 13:47

answered Jul 9, 2014 at 13:02

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jeff Over a year ago

basically this is not null, as the users are inputting only spaces and it is allowed on our system, which my current code must handle.

Alex Poole Over a year ago

@user3820337 - it is null after it has been through trim.

jeff Over a year ago

Thanks for this, it's working fine now, one problem though I have seen some entries where users are inputting enter instead of space, does TRIM also handle next lines?

Alex Poole Over a year ago

@user3820337 - by default trim removes spaces. Updated with replace and regexp_replace variations.

Arron · Accepted Answer · 2014-07-09 13:06:50Z

0

Best solution would be to validate and filter that kind of input before it even enters the database. But as that is not the case, a solution that could work:

regexp_matches()

answered Jul 9, 2014 at 13:06

Arron

9166 silver badges15 bronze badges

Collectives™ on Stack Overflow

Using CASE on empty string

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related