" 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 13 days ago    Bishnu Basyal

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