MATCHES and LIKE in SQL conditions

Informix^® supports MATCHES and LIKE in SQL statements, while Microsoft™ SQL SERVER supports the LIKE statement only.

The MATCHES operator of Informix uses the star (*), question mark (?) and square braces ([ ]) wildcard characters. The LIKE operator of SQL SERVER offers the percent (%), underscore (_) and square braces ([ ]) wildcard characters:

( col MATCHES 'Smi*' AND col NOT MATCHES 'R?x[a-z]' )
( col LIKE 'Smi%' AND col NOT LIKE 'R_x[a-z]' )

The LIKE operator of SQL Server does not evaluate to true with CHAR/NCHAR columns, if the LIKE pattern is provided as a UNICODE string literal (with the N prefix) and the search pattern matches the value in the column (without an ending % wildcard for example). See the following test:

CREATE TABLE mytable ( k INT, nc NCHAR(20) )
INSERT INTO mytable VALUES ( 1, N'abc' )
SELECT * FROM mytable WHERE nc = 'abc' -- one row is returned
SELECT * FROM mytable WHERE nc = N'abc' -- one row is returned
SELECT * FROM mytable WHERE nc LIKE 'abc' -- one row is returned
SELECT * FROM mytable WHERE nc LIKE N'abc' -- no rows are found
SELECT * FROM mytable WHERE nc LIKE N'abc%' -- one row is returned 

This can be an issue because the SQL Server driver will by default automatically add an N prefix before all string literals in SQL statements. See Microsoft SQL Server documentation for more details about the LIKE semantics regarding blank padding and see also CHARACTER data types for the N prefix usage: You might consider setting the snc.widechar FGLPROFILE parameter to false if you are using CHAR/VARCHAR types.