String Operations Cheat Sheet > Querying with SQL

 

String Operations

Consider the case of e-commerce platforms. We generally search for the products on the basis of product name. But while searching, we need not enter the full name. For example, typing “mobiles” in a search bar will fetch thousands of results. How to get the data on the basis of only a part of the string? Let’s learn about it!

Database The database contains a

product

table that stores the data of products like name, category, price, brand and rating. You can check the schema and data of

product

table in the code playground.

LIKE Operator

LIKE

operator is used to perform queries on strings. This operator is especially used in

WHERE

clause to retrieve all the rows that match the given pattern.

We write

patterns

using the following

wildcard characters

:

Symbol	Description	Example
Percent sign ( % )	Represents zero or more characters	ch% finds ch, chips, chocolate..
Underscore ( _ )	Represents a single character	_at finds mat, hat and bat

Common Patterns

Pattern	Example	Description
Exact Match	WHERE name LIKE "mobiles"	Retrieves products whose name is exactly equals to "mobiles"
Starts With	WHERE name LIKE "mobiles%"	Retrieves products whose name starts with "mobiles"
Ends With	WHERE name LIKE "%mobiles"	Retrieves products whose name ends with "mobiles"
Contains	WHERE name LIKE "%mobiles%"	Retrieves products whose name contains with "mobiles"
Pattern Matching	WHERE name LIKE "a_%"	Retrieves products whose name starts with "a" and have at least 2 characters in length

Syntax

1

2

3

4

5

6

SELECT

*

FROM

table_name

WHERE

c1 LIKE matching_pattern;

SQL

Examples

1. Get all the products in the "Gadgets" category from the
   product
   table.

1

2

3

4

5

6

SELECT

*

FROM

product

WHERE

category LIKE "Gadgets";

SQL

Output

name	category	price	brand	rating
Smart Watch	Gadgets	17000	Apple	4.9
Smart Cam	Gadgets	2600	Realme	4.7
Smart TV	Gadgets	40000	Sony	4.0
Realme Smart Band	Gadgets	3000	Realme	4.6

2. Get all the products whose
   name
   starts with "Bourbon" from the
   product
   table.

1

2

3

4

5

6

SELECT

*

FROM

product

WHERE

name LIKE "Bourbon%";

SQL

Here

%

represents that, following the string "Bourbon", there can be 0 or more characters.

Output

name	category	price	brand	rating
Bourbon Small	Food	10	Britannia	3.9
Bourbon Special	Food	15	Britannia	4.6
Bourbon With Extra Cookies	Food	30	Britannia	4.4

3. Get all smart electronic products i.e., 
   name
    contains "Smart" from the
   product
   table.

1

2

3

4

5

6

SELECT

*

FROM

product

WHERE

name LIKE "%Smart%";

SQL

Here,

%

before and after the string "Smart" represents that there can be 0 or more characters succeeding or preceding the string.

Output

name	category	price	brand	rating
Smart Watch	Gadgets	17000	Apple	4.9
Smart Cam	Gadgets	2600	Realme	4.7
Smart TV	Gadgets	40000	Sony	4
Realme Smart Band	Gadgets	3000	Realme	4.6

4. Get all the products which have exactly 5 characters in
   brand
    from the 
   product
    table.

1

2

3

4

5

6

SELECT

*

FROM

product

WHERE

brand LIKE "_____";

SQL

Output

name	category	price	brand	rating
Blue Shirt	Clothing	750	Denim	3.8
Black Jeans	Clothing	750	Denim	4.5
Smart Watch	Gadgets	17000	Apple	4.9
...	...	...	...	...

Note

The percent sign(%) is used when we are not sure of the number of characters present in the string. If we know the exact length of the string, then the wildcard character underscore(_) comes in handy.

Try it Yourself!

Put your learning into practice and try fetching the products based on the different patterns:

Write a query for each of the below patterns.

• category
   is exactly equal "Food".
• name
   containing "Cake".
• name
  ends with "T-Shirt".
• name
  contains "Chips".
• category
   contains exactly 4 characters.