LIKE Operator - ( Oracle Misc )

In Oracle SQL, the LIKE operator is used to match a string value to a specified pattern using wildcards. This operator is commonly used in conjunction with the SELECT statement to retrieve data based on specified patterns.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

Here, columnN is the column name that you want to match against the pattern defined by pattern. The pattern can include one or more wildcard characters such as %, _, [ ], or [^]. For example, the % character matches any string of zero or more characters, while the _ character matches any single character.

Example

Consider a table called students that contains the following data:

id	name	age
1	Alice	20
2	Bob	25
3	Carol	22
4	David	30
5	Eve	18

To retrieve all rows where the name begins with the letter 'A', we can use the following SQL query:

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

This query will return the following result:

id	name	age
1	Alice	20

Output

# Query Result
id  name    age
1   Alice   20

Explanation

In the above example, we used the LIKE operator to match all names that began with the letter 'A'. We used the % wildcard character to match any string of zero or more characters following the letter 'A'. The result set returned only the row with id=1, where the name was 'Alice'.

Use

The LIKE operator is commonly used in Oracle SQL to retrieve data based on a string pattern. It is useful when searching for data where the exact match is not known, but a pattern can be determined. For example, searching for all names that begin with a certain letter, end with a certain sequence, or contain a certain word.

Important Points

• The LIKE operator is used to match a given value to a specified pattern.
• Wildcard characters such as %, _, [ ], or [^] can be used to specify patterns.
• The LIKE operator is case-insensitive by default, but this can be changed using the COLLATE keyword.
• The LIKE operator is commonly used in Oracle SQL to retrieve data based on a string pattern.

Summary

In summary, the LIKE operator in Oracle SQL is used to match a string value to a specified pattern using wildcards. It is commonly used in conjunction with the SELECT statement to retrieve data based on specified patterns. The LIKE operator is case-insensitive by default, and is useful for searching for data where the exact match is not known, but a pattern can be determined.