CODEWITHSUNDEEP
sqloracle-database
Débora
Débora

Reputation: 5952

Oracle DB LIKE query on NULL values

This question is about Oracle DB. I want to know how Oracle DB query LIKE clause works on NULL values. 

Select * From table_name where column_name like 'text%'

In above query, how the Oracle db treat to rows with null value for column 'column_name'? Also how about 'NOT LIKE'.

Also, I observed that rows having NULL values are not selected for the following query. 

Select * From table_name where column_name NOT LIKE 'text%'  .

I don't know why rows having NULL values for the column are not in results even though they are null and hence not like 'text%' .

Upvotes: 2

Views: 9424

Answers (4)

Prasad Dhananjaya
Prasad Dhananjaya

Reputation: 141

Try this,

SELECT *
FROM table_name
WHERE NVL(column_name,1) NOT LIKE NVL('',2) -- '' OR NULL you can use

Upvotes: 1

Prasad Dhananjaya
Prasad Dhananjaya

Reputation: 141

Try this,

SELECT *
FROM table_name
WHERE NVL(column_name,1) NOT LIKE NVL('',2) -- '' OR NULL you can use

Upvotes: 0

Suraj
Suraj

Reputation: 3137

First one,
When you search for column_name like 'text%', db search for string that starts with "text" not other string, it doesn't matter what will come after the text. It could be anything like text123,text stack etc.

Second one,
When you search for NOT LIKE 'text%', db search for all the columns that should not be started with text, it the column value have text it will not be in the result. it is like "atext", it will be appear in the search results.

So in both condition NULL values never match so they don't come in the results.

Hope it will help.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

NULL values basically fail all comparisons. The general idea is that NULL means "I don't know what the value is". So, when you use like with the pattern 'text%', the answer is "I don't know what the value is". It is NULL.

And if you use not like, the answer is the same "I don't know what the result is".

That is how NULLs work. Even with like and not like. Even with Oracle.

Upvotes: 4

Related Questions