SQL LIKE in Statement

Definition of SQL LIKE in Statement

SQL like in statement is used with where the condition for finding all rows which were matching pattern row, which was we have defined in like and where condition. There are two different types of wildcards available in SQL. At the time of pattern matching, the regular characters need to match with the characters exactly. The SQL, like in a statement, is used to substitute the single or more characters from a string.

Overview of SQL like in the statement

• SQL-like statements are also used with SQL queries. We are using the in a statement to retrieve the records we have defined into the in condition.
• It is slightly different from the normal like operator which we have used on a single column.
• The SQL, like the statement, includes the wildcard or regular characters. Basically, SQL, like in a statement, will check whether the character string will match the specified pattern or not. The SQL-like pattern is included in the query, which we have specified in the where condition within the statement.
• At the time of using, if our argument is not character type, then the SQL engine will convert it into the character type.
• The SQL-like statement is an ISO and ANSI standard that was used to retrieve all the table rows which were we have used in the query. We can retrieve data from quoted as well as a normal strings.
• The SQL like in the statement is supporting the different kinds of quoted-string characters. This will allow us to search a text-based as well as integer string of a specified pattern.
• Basically, it is also used with the where clause in select, update, and delete SQL statements. We can use in a statement to find the specified pattern data from the column which we have defined in the query.
• It is defined to retrieve data as per the specified pattern which we have used in the query.
• Using a wildcard operator in a like clause, pattern matching is very easy as compared to using the = and != comparison operator of string.
• At the time of pattern matching, the regular characters need to match with the characters which were specified in the string of characters exactly.
• SQL, like in the operator, is very useful and important to retrieve specified rows from tables.

How to Use SQL like in statement?

• We can use SQL like an operator to find the matched string by using a specified pattern that we are using in the query.
• By using the wildcard operator in SQL, like in the statement, we can find specified rows from the SQL table. The rows will return which match the specified criteria of the like statement.
• We can use it by using select, updating, and deleting queries. To update and delete records of specified criteria from the table.
• We can also use the escape character with SQL like in operator which was treat the wildcard operator as regular characters.
• We can also use it for selecting, deleting, and updating specified records that are specified in the pattern.
• We can also use the order by and where clause. We can use SQL like in statement with select and where statement to find the matched pattern string rows from the specified table.
• The SQL, like the statement, is returning the number of rows when it matches the specified condition which we have defined in the query.
• Suppose we want to select, insert or delete a specified pattern of records from the table, then SQL, like in the statement, is the best choice as compared to other states which we are using in SQL.

Below is the syntax as follows. In the below example, we can see that IN and like will work the same when we define the specified condition. Below syntax shows SQL select by using SQL like in statement.

Syntax –

• Select name_of_column1, name_of_column2, …, name_of_columnN from name_of_table where name_of_column like ‘%xxx%’;
• Select name_of_column1, name_of_column2, …, name_of_columnN from name_of_table where name_of_column like ‘_xxx_’;
• Select name_of_column1, name_of_column2, …, name_of_columnN from name_of_table where name_of_column IN (val1, val2, …, valN);

Below syntax shows SQL delete.

Syntax –

• Delete from name_of_table where name_of_column like ‘%xxx%’;
• Delete from name_of_table where name_of_column like ‘_xxx_’;
• Delete from name_of_table where name_of_column in (val1, val2, …, valN);

Below syntax shows SQL delete as follows.

Syntax –

• Update name_of_table set name_of_column = ‘values’ where name_of_column like ‘%xxx%’;
• Update name_of_table set name_of_column = ‘values’ where name_of_column like ‘_xxx_’;
• Delete from name_of_table where name_of_column In (val1, val2, …, valN);

select * from SQL_like_in where name like '%B%';

select * from SQL_like_in where name like '_B_';

select * from SQL_like_in where id in (101);

Examples

• In the below example, we are using a select query with SQL like in the statement are as follows.

select * from SQL_like_in where name like 'P%';

select * from SQL_like_in where name like 'P__';

select * from SQL_like_in where id in (102);

• In the below example, we are using a delete query with SQL like in the statement. In the below first example, we are using the ‘%C’ and ‘__C’ wildcard operators.

Update SQL_like_in set id = 110 where name like ‘%C’;

Update SQL_like_in set id = 110 where name like ‘__C’;

Update SQL_like_in set id = 110 where id in (101);

Select * from SQL_like_in;

• In the below example, we are using a delete query with SQL like in the statement. In the below first example, we are using ‘%C’ and ‘__C’ wildcard operators as follows.

Delete from SQL_like_in where name like ‘%C’;

Delete from SQL_like_in where name like ‘__C’;

Delete from SQL_like_in where id in (110, 102);

Select * from SQL_like_in;

Conclusion

The SQL, like the statement, is, supporting the different kinds of quoted-string characters. It is used with where the condition for finding all rows which were matching pattern row which was we have defined in like and where condition.

Recommended Article

This is a guide to SQL LIKE in Statement. Here we discuss the definition, overview, How to Use SQL like in statements, examples along with code implementation and output. You may also have a look at the following articles to learn more –

1. PL/SQL Raise Exception
2. PL/SQL LIKE
3. PL/SQL UNION
4. SQL WAITFOR