" Start your business on internet, we are here to help you "

We are Software Development and Technical Service providing company from kathmandu Nepal. We use latest technologies to fulfill your requirements

LIKE operator in SQL

SQL     Published at 2 months ago    Bishnu Basyal

LIKE operator in SQL

The SQL LIKE operator is used in a WHERE clause to search for a specified pattern in a column using wildcard operators.

LIKE operator is used in search case.

There are two wildcards used with the LIKE operator:

1) The percent sign (%)
    The percent sign represents zero, one, or multiple characters.
2) The underscore (_)
    The underscore represents a single character.

The percent sign (%) and the underscore (_) can also be used to form combinations.

 

General LIKE operator syntax

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

 

Different LIKE operators with '%' and '_' wildcards:

WHERE student_name LIKE 's%'     

Finds any values that starts with "s"

 

WHERE student_name LIKE '%s'     

Finds any values that ends with "s"

 

WHERE student_name LIKE '%su%'     

Finds any values that have "su" in any position

 

WHERE student_name LIKE '_a%'     

Finds any values that have "a" in the second position

 

WHERE student_name LIKE 'a_%_%'     

Finds any values that starts with "a" and are at least 3 characters in length

 

WHERE student_name LIKE 'b%u'    

Finds any values that starts with "b" and ends with "u"

 

Few examples:

selects all students with a student_name starting with "s":

SELECT * FROM students
WHERE student_name LIKE 's%';

 

selects all students with a student_name ending with "s":

SELECT * FROM students
WHERE student_name LIKE '%s';

 

selects all students with a student_name that have "su" in any position:

SELECT * FROM students
WHERE customer_name LIKE '%su%';

 

selects all students with a student_name that have "r" in the second position:

SELECT * FROM students
WHERE student_name LIKE '_r%'; 

 

selects all students with a student_name that starts with "a" and are at least 3 characters in length:

SELECT * FROM students
WHERE student_name LIKE 'a_%_%';  

 

selects all students with a student_name that starts with "a" and ends with "o":

 SELECT * FROM students
WHERE student_name LIKE 'a%o';

 

selects all students with a student_name that NOT starts with "a":

SELECT * FROM students
WHERE student_name NOT LIKE 'a%'; 

 

selects all students with a student_name and student_address that starts with "a":

SELECT * FROM students
WHERE student_name and student_address LIKE 'a%';

 

selects all students with a student_name or student_address that starts with "a":

SELECT * FROM students
WHERE student_name or student_address LIKE 'a%';

 

Thank you......

To Top