SQL | LIKE

The LIKE operator in SQL is used in a WHERE clause to search for a specified pattern within a column. It's particularly useful when you're unsure about the exact value you're searching for. The two main wildcards used in conjunction with the LIKE operator are:

% - Represents zero, one, or multiple characters
_ - Represents a single character

Here are some examples to better understand the usage:

1. Using %:

Finding any values that start with "a":

SELECT * FROM table_name WHERE column_name LIKE 'a%';

Finding any values that end with "a":

SELECT * FROM table_name WHERE column_name LIKE '%a';

Finding any values that have "or" in any position:

SELECT * FROM table_name WHERE column_name LIKE '%or%';

2. Using _:

Finding any values that have "o" in the second position:

SELECT * FROM table_name WHERE column_name LIKE '_o%';

Finding any values that are three characters long and start with "an":
```
SELECT * FROM table_name WHERE column_name LIKE 'an_';
```

Combining % and _:

Finding any values that start with "a", have "o" in the third position, and end in "n":
```
SELECT * FROM table_name WHERE column_name LIKE 'a_o%n';
```

It's essential to remember that the LIKE pattern matching is case-insensitive in some databases (like MySQL with the default collation) and case-sensitive in others (like PostgreSQL). Always be aware of how your specific database system treats this.

In scenarios where case-sensitivity matters and you're working with a database system where LIKE is case-sensitive, you might need to convert both the column data and the match string to either uppercase or lowercase using functions like UPPER() or LOWER():

SELECT * FROM table_name WHERE UPPER(column_name) LIKE UPPER('a%');

This ensures a case-insensitive match across systems where LIKE is typically case-sensitive.

SQL LIKE operator examples:
- Description: The SQL LIKE operator is used to search for a specified pattern in a column. It is often used with wildcard characters to perform flexible searches.
- Example Code:
```
SELECT * FROM employees
WHERE last_name LIKE 'Sm%';
```
How to use LIKE in SQL:
- Description: The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It allows for pattern matching using wildcard characters.
- Example Code:
```
SELECT * FROM products
WHERE product_name LIKE 'A%';
```
Wildcard characters with LIKE in SQL:
- Description: Wildcard characters like % (matches any sequence of characters) and _ (matches any single character) can be used with LIKE to create flexible patterns for matching.
- Example Code:
```
SELECT * FROM customers
WHERE contact_name LIKE 'M%y';
```
Using LIKE for partial string matching:
- Description: LIKE is often used for partial string matching, allowing you to find rows where a specific pattern occurs within a column.
- Example Code:
```
SELECT * FROM products
WHERE product_name LIKE '%apple%';
```
Case-sensitive and case-insensitive LIKE in SQL:
- Description: By default, LIKE is case-insensitive. However, some databases support case-sensitive searches by using case-sensitive collations.
- Example Code:
```
-- Case-insensitive
SELECT * FROM users
WHERE username LIKE 'john%';

-- Case-sensitive (example for SQL Server)
SELECT * FROM users
WHERE username COLLATE Latin1_General_CS_AS LIKE 'John%';
```
LIKE with multiple conditions in SQL:
- Description: You can use the AND or OR operators to combine multiple LIKE conditions in a SQL query.
- Example Code:
```
SELECT * FROM orders
WHERE customer_name LIKE 'A%'
AND order_date LIKE '2023%';
```
Combining LIKE with other operators:
- Description: LIKE can be combined with other operators (e.g., =, <>, >, <) to create more complex conditions in SQL queries.
- Example Code:
```
SELECT * FROM products
WHERE product_name LIKE 'A%'
AND unit_price > 10;
```
Using NOT LIKE in SQL:
- Description: NOT LIKE is used to exclude rows that match a specified pattern.
- Example Code:
```
SELECT * FROM employees
WHERE job_title NOT LIKE '%Manager%';
```
Escape characters with LIKE in SQL:
- Description: The ESCAPE keyword is used with LIKE to specify an escape character, allowing you to search for literal occurrences of wildcard characters.
- Example Code:
```
SELECT * FROM products
WHERE product_name LIKE '10!_%' ESCAPE '!';
```
LIKE and character classes in SQL:
- Description: LIKE can be used with character classes, such as [a-z] or [0-9], to match specific ranges of characters.
- Example Code:
```
SELECT * FROM customers
WHERE phone_number LIKE '[2-9]%';
```
LIKE and numeric patterns in SQL:
- Description: Although LIKE is often associated with string matching, it can also be used with numeric patterns.
- Example Code:
```
SELECT * FROM orders
WHERE order_number LIKE '2023%';
```
LIKE and full-text search in SQL Server:
- Description: In SQL Server, the CONTAINS or FREETEXT predicates are often used for full-text searches instead of LIKE for better performance.
- Example Code:
```
SELECT * FROM documents
WHERE CONTAINS(document_content, 'SQL Server');
```