CODEWITHSUNDEEP
sqlcaseoracle-sqldeveloper
jeff
jeff

Reputation: 23

Using CASE on empty string

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!

Upvotes: 2

Views: 17419

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

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.

Upvotes: 5

Arron
Arron

Reputation: 916

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

Upvotes: 0

Related Questions