CODEWITHSUNDEEP
sqloracle-database
fandang
fandang

Reputation: 607

Is using like '%' the same as saying "IS NOT NULL"?

If not, why would somebody use abc like '%' ?

Upvotes: 0

Views: 109

Answers (2)

Boneist
Boneist

Reputation: 23588

Effectively, yes. I've seen it used when people want to pass in a like condition parameter, so that they can pass in a specific value, a partial value or no value into a query that's of the form select ... from ... where some_col like :some_parameter.

ETA: I would point out that if I saw where some_col like '%' in production code when the person who wrote it really meant where some_col is not null I think I'd have plenty of words to say to them! The latter condition is much more self-descriptive and clear in its intent, and won't leave people scratching their heads wondering what the person was thinking of when they wrote it!

Upvotes: 3

Ditto
Ditto

Reputation: 3344

Just test it?

  drop table junk;

  create table junk ( id  number, col  varchar2(10) );

  insert into junk values ( 123, null );
  insert into junk values ( 234, ' ' );
  insert into junk values ( 345, 'asdf' );

  commit;

  select *
    from junk
   where col like '%'
  /

          ID COL
  ---------- ----------
         234
         345 asdf

So no, as with most things Oracle, direct compares won't see NULL ... If you want to detect for NULL, use IS NULL. If you want to exclude NULLs, be clear, and include IS NOT NULL .. (even if something like this "looks like" it eliminates it) ;) By using "IS NOT NULL" it'll be much clearer to anyone reading the code.

Relevant links: http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions007.htm#SQLRF52142

A percent sign (%) in the pattern can match zero or more characters (as opposed to bytes in a multibyte character set) in the value. The pattern '%' cannot match a null.

Upvotes: 4

Related Questions