SQL Like operator

The LIKE operator in SQL is used to perform simple pattern matching in queries. It allows you to search for strings that match a specified pattern. The syntax is:

WHERE column LIKE pattern

Some examples:

Starts With

SELECT * FROM customers WHERE name LIKE 'A%';

This would return all customers whose names start with A. The percent sign (%) matches any sequence of zero or more characters.

Ends with

SELECT * FROM products WHERE name LIKE '%bike';

This would match any values in the name column that end with 'bike', such as 'mountain bike', 'racing bike', 'kids bike' etc.

Contains

SELECT * FROM products WHERE name LIKE '%Bike%'; 

This would return any products that contain the word 'Bike' anywhere in the name.

Matches character

SELECT * FROM employees WHERE last_name LIKE 'Smi_h';

The underscore '_' matches exactly one character. This finds names like Smith or Smath.

The LIKE operator is case-insensitive by default, so 'a' matches both 'a' and 'A'.

Regex usage

Some databases also support LIKE with regular expressions for more advanced matching. For example:

SELECT * FROM logs WHERE ip_address REGEXP '^192\.168\.1\.[0-9]{1,3}$';

This uses a regex to match IP addresses in a range.

Here are some tips for using LIKE:

- NOT LIKE can be used to exclude results matching a pattern.

- % and _ can be escaped with backslashes if you need to match literal instances.

- ILIKE provides a case-sensitive version in Postgres and other databases.

- Matching the start and end of strings precisely can be faster than leading/trailing % wildcards.

- Dynamic LIKE clauses can parameterize the pattern from application code.

- Computed columns can generate the LIKE clause from other columns.

- Function indexes can speed up LIKE queries dramatically in some databases.

- Full-text search is often better than LIKE for advanced text matching.

- Pattern matching operations like SIMILAR TO and POSIX regular expressions are available in some databases.

The LIKE operator is a simple but powerful tool for exact and partial matching scenarios. With some creativity, it can handle many use cases without needing complex regular expressions or full-text search. Mastering the various pattern-matching options in SQL helps build robust and flexible queries.