CODEWITHSUNDEEP
sqloracle-database
oria
oria

Reputation: 21

oracle sql - how to get columns which contain string or null

I have table with 3 columns:

enter image description here

I want to get all records where type like '%' and is_connect like 'N', Including rows where type IS NULL or contains a string.

My query:

SELECT * 
FROM OFFER 
WHERE TYPE LIKE '%' 
AND IS_CONNECT LIKE 'N';

I got the rows where type is NOT NULL, but how can I get all rows, even when the type IS NULL?

Upvotes: 2

Views: 313

Answers (4)

Aritra Bhattacharya
Aritra Bhattacharya

Reputation: 780

just remove the like clause if you want all the entries for type :

SELECT * FROM OFFER WHERE (TYPE LIKE '%'  or TYPE is NULL) AND IS_CONNECT = 'N';

Upvotes: 1

Gbenga A
Gbenga A

Reputation: 9

Try this. 

Where N is you first alphabet. 
SELECT * FROM OFFER 
WHERE TYPE is null 
AND IS_CONNECT LIKE 'N%';

Where N is the last alphabet in your type. 
SELECT * FROM OFFER 
WHERE TYPE is null 
AND IS_CONNECT LIKE '%N';

__ BCG14

Upvotes: 1

cableload
cableload

Reputation: 4385

You were almost there..Just use an OR condition to include null check...Also, you didnt have a 'N%' after is_connect like

SELECT * FROM OFFER WHERE (TYPE LIKE '%'  or TYPE is NULL) AND IS_CONNECT LIKE 'N%'

Upvotes: 0

Allan
Allan

Reputation: 17429

If you want to get all or the rows where there is either a value or no value, simply exclude the field from your where clause:

SELECT * FROM OFFER WHERE IS_CONNECT = 'N';

Upvotes: 1

Related Questions