CODEWITHSUNDEEP
sqloracle-databaseconditional-statements
Sam
Sam

Reputation: 513

Oracle : IN and OR

I've a scenrio which process many data in Oracle database. In some cases, the variable Sec_email will contain many values and in some cases Sec_email will contain null or ' '. so can please any one tell me how to write a query for this?

I tried with

(C.SECONDARY_EMAIL IN ('?,?') OR '' = '' )

where C is the Client table.

When I use this i get the count as 0.

Upvotes: 0

Views: 125

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

One obvious problem is that Oracle (by default) treats empty strings as NULL. So: '' = '' is the same as NULL = NULL, which is never true.

Arrgh.

In any case, you are probably constructing the query, so use is null instead:

(C.SECONDARY_EMAIL IN ('?,?') OR '' IS NULL

I think the real problem, though, is the first comparison. The IN list has one element with a constant, not two (but perhaps that is your intention). If you want to put a variable number of values for comparison, one method uses regular expressions. For instance:

C.SECONDARY_EMAIL REGEXP_LIKE '^val1|val2|val3$' or '' IS NULL

Upvotes: 1

Mikhailov Valentin
Mikhailov Valentin

Reputation: 1102

If you would like to get a list of values when some of them is null you should use:

("some other conditions" OR C.SECONDARY_EMAIL IS NULL)

The question is if it is not null and not ' ' value what you are expecting, if it should be some king of pattern you should use regular expression:

regexp_like(C.SECONDARY_EMAIL, '^(.+?[,]+?)+$')

Also, if you have a few conditions in where clause use should use brackets to group you conditions null check and another one.

All conditions i this case will be divided by OR.

(C.SECONDARY_EMAIL IS NULL OR regexp_like(C.SECONDARY_EMAIL, '^(.+?[,]+?)+$'))

or

(C.SECONDARY_EMAIL IS NULL OR  regexp_like(C.SECONDARY_EMAIL, '^(.+?[,]+?)+$')
 OR C.SECONDARY_EMAIL = ' ')

Upvotes: 0

Rahul
Rahul

Reputation: 77876

You can perform a not null check before the IN comparison like

Sec_email is not null and C.SECONDARY_EMAIL IN (...

Upvotes: 1

Related Questions