Like '%' Does Not Accept Null Value

like '%' does not accept NULL value

You can use coalesce to treat null like an empty string:

where COALESCE([table].[column],'') like '<parameter>'

On SQL Server, you can also use IsNull:

where IsNull([table].[column],'') like '<parameter>'

How not to exclude null from where not like condition?

just add NULL as a specific case. [edited to show how to generalise this approach to OPs more complex query]

Where
    T1.OUT_NO = T2.OUT_NO 
AND T3.OUT_NO = T1.OUT_NO 
AND CAUSE_CAP.CAUSE_NO NOT IN (1,3,5,7,9) 
AND ("T1"."TIME_STAMP">=TO_DATE ('01-04-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "T1"."TIME_STAMP"<TO_DATE ('06-04-2013 23:59:59', 'DD-MM-YYYY HH24:MI:SS')) 
AND NOT (CAUSE_CAP.CAUSE_NO = 13 AND START_TABLE.TABLE_NO = 83)
AND (T2.Comments IS NULL OR 
        (T2.Comments not like '%ABC%'
         AND T2.Comments not like '%XYZ%')
    )

Is SQL LIKE NULL valid syntax for all database?

Yes, LIKE NULL is valid in all RDBMS. LIKE is an operator followed by a string and a string can be null; so no problem.

Comparing a value to NULL, no matter what operator (<, <=, =, <>, LIKE, etc. - except for IS which is especially made to compare with NULL), results in UNKNOWN. UNKNOWN is not TRUE, so the condition is not met in case of NULL. And anyway, in case @firstName contains NULL, @firstName IS NULL evaluates to TRUE, so it doesn't even matter what LIKE @firstName results in then (because @firstName IS NULL OR firstName LIKE @firstName is TRUE when at least one of the two conditions is TRUE).

Behaviour of NOT LIKE with NULL values

About NULL

'anything' NOT LIKE NULL yields NULL, not TRUE.

And only TRUE qualifies for filter expressions in a WHERE clause.

Most functions return NULL on NULL input (there are exceptions). That's the nature of NULL in any proper RDBMS.

If you desire a single expression, you could use:

AND   (column_default LIKE 'nextval%')  IS NOT TRUE;

That's hardly shorter or faster, though. Details in the manual.

Proper query

Your query is still unreliable. A table name alone is not unique in a Postgres database, you need to specify the schema name in addition or rely on the current search_path to find the first match in it:

Related:

• How does the search_path influence identifier resolution and the "current schema"

SELECT column_name
FROM   information_schema.columns
WHERE  table_name = 'hstore1'
AND    table_schema = 'public'   -- your schema!
AND   (column_default IS NULL OR
       column_default NOT LIKE 'nextval%');

Better, but still not bullet-proof. A column default starting with 'nextval' does not make a serial, yet. See:

• Auto increment table column

To be sure, check whether the sequence in use is "owned" by the column with pg_get_serial_sequence(table_name, column_name).

I rarely use the information schema myself. Those slow, bloated views guarantee portability across major versions - and aim at portability to other standard-compliant RDBMS. But too much is incompatible anyway. Oracle does not even implement the information schema (as of 2015).

Also, useful Postgres-specific columns are missing in the information schema. For this case I might query the the system catalogs like this:

SELECT *
FROM   pg_catalog.pg_attribute a
WHERE  attrelid = 'table1'::regclass
AND    NOT attisdropped   -- no dropped (dead) columns
AND    attnum > 0         -- no system columns
AND    NOT EXISTS (
   SELECT FROM pg_catalog.pg_attrdef d
   WHERE  (d.adrelid, d.adnum) = (a.attrelid, a.attnum)
   AND    d.adsrc LIKE 'nextval%'
   AND    pg_get_serial_sequence(a.attrelid::regclass::text, a.attname) <> ''
   );

Faster and more reliable, but less portable.

The manual:

The catalog pg_attrdef stores column default values. The main
information about columns is stored in pg_attribute (see below). Only
columns that explicitly specify a default value (when the table is
created or the column is added) will have an entry here.

'table1'::regclass uses the search_path to resolve the name, which avoids ambiguity. You can schema-qualify the name to overrule: 'myschema.table1'::regclass.

Related:

• Find the referenced table name using table, field and schema name
• Get the default values of table columns in Postgres?

NULL values inside NOT IN clause

Query A is the same as:

select 'true' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = null

Since 3 = 3 is true, you get a result.

Query B is the same as:

select 'true' where 3 <> 1 and 3 <> 2 and 3 <> null

When ansi_nulls is on, 3 <> null is UNKNOWN, so the predicate evaluates to UNKNOWN, and you don't get any rows.

When ansi_nulls is off, 3 <> null is true, so the predicate evaluates to true, and you get a row.

Use like '%' and match NULL values with NUMBER columns

My procedure takes a lot of parameters, and does a select with all of them. They can all have a value or be null, so if they're null they're replaced with '%'.

That seems like you're making life hard for yourself. Leave them null, then do:

where (param1 is null or t.col1 like param1)
and (param2 is null or t.col2 like param2)

If param1 (the procedure argument; life is simpler when your parameter/variable names and column names are different... so I've changed the column names to make it a bit clearer) is null it is basically ignored* and all rows pass that part of the filter, whether the column value is null or not null. If param2 is not null then the is null check for that fails and only rows with (not-null) column values that match param2 value meet that part of the filter.

_{* Conditions in an or can be evaluated in any order; putting the is null check first doesn't necessarily mean the like won't be evaluated - but the optimiser is pretty smart about that sort of thing}

LIKE and NULL in WHERE clause in SQL

You can use condition like this in you where clause

where @Keyword is null or CustomerName like '%' + @Keyword + '%' 

Empty string vs NULL

Some differences between them:

• NULL can be assigned to any type, as opposed to empty string which won't be compatible with date/numerical fields.
• NULL is an UNKNOWN value, it doesn't have a value as opposed to an empty string, which is a value, but empty one.
• As far as I'm aware of, NULL shouldn't capture memory as opposed to an empty string which does.
• null = null will result in null as opposed to ''='' which will result in TRUE.

Oracle DB LIKE query on NULL values

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.

---

Related Topics