SQL wildcards underscore ( _ )

Underscore Operator

The underscore character ( _ ) represents a single character to match a pattern from a word or string. More than one ( _ ) underscore characters can be used to match a pattern of multiple characters.

Example

Sample table: customer

To get 'cust_code', 'cust_name', 'cust_city' and 'cust_country' from the table 'customer' with following conditions -

1. the 'cust_name' must initiated with the letter 'R',

2. the third letter of 'cust_name' must be 'm',

3. and the second letter of 'cust_name' may be any,

the following sql statement can be used :

SELECT cust_code, cust_name, cust_city, cust_country
-- Selecting specific columns: cust_code, cust_name, cust_city, cust_country
FROM customer
-- From the table named "customer"
WHERE cust_name LIKE 'R_m%';
-- Where the value in the column cust_name starts with 'R', followed by any single character ('_'), and then followed by 'm' and any number of characters after that

Explanation:

• SELECT cust_code, cust_name, cust_city, cust_country: This specifies that we want to retrieve specific columns (cust_code, cust_name, cust_city, cust_country) from the table.


• FROM customer: This indicates the table from which we want to retrieve the data. In this case, the table is named "customer".


• WHERE cust_name LIKE 'R_m%': This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the column cust_name starts with 'R', followed by any single character ('_'), and then followed by 'm' and any number of characters after that should be included in the result set. The '_' symbol is a wildcard that matches any single character, and the % symbol is a wildcard that matches any sequence of characters.

Output:

CUST_CODE  CUST_NAME            CUST_CITY       CUST_COUNTRY
---------- -------------------- --------------- -------------------
C00007     Ramanathan           Chennai         India
C00009     Ramesh               Mumbai          India

Relational Algebra Expression:

Relational Algebra Tree:

Sql wildcards underscore ( _ ) multiple characters

Sample table: customer

To get 'cust_code', 'cust_name', 'cust_city' and 'cust_country' from the table 'customer' with following conditions -

1. the first three letters of 'cust_name' may be any letter

2. the forth letter of 'cust_name' must be 'l'

3. and the the rest may be any

the following sql statement can be used :

SELECT cust_code, cust_name, cust_city, cust_country
-- Selecting specific columns: cust_code, cust_name, cust_city, cust_country
FROM customer
-- From the table named "customer"
WHERE cust_name LIKE '___l%';
-- Where the value in the column cust_name starts with any three characters ('___'), followed by 'l' and any number of characters after that

Explanation:

• SELECT cust_code, cust_name, cust_city, cust_country: This specifies that we want to retrieve specific columns (cust_code, cust_name, cust_city, cust_country) from the table.


• FROM customer: This indicates the table from which we want to retrieve the data. In this case, the table is named "customer".


• WHERE cust_name LIKE '___l%': This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the column cust_name starts with any three characters ('___'), followed by 'l' and any number of characters after that should be included in the result set. The '___' symbol represents any three characters, and the 'l' specifies that the next character must be 'l'.

Output:

CUST_CODE  CUST_NAME            CUST_CITY       CUST_COUNTRY
---------- -------------------- --------------- ------------------
C00023     Karl                 London          UK
C00006     Shilton              Torento         Canada

Relational Algebra Expression:

Relational Algebra Tree:

Sql wildcards underscore ( _ ) for specific length

Sample table: customer

To get 'cust_code', 'cust_name', 'cust_city' and 'cust_country' from the table 'customer' with following conditions -

1. the first three letters of 'cust_name' may be any letter

2. the forth letter of 'cust_name' must be 'l'

3. and the the string must be a length of 4 letters

the following sql statement can be used :

SELECT cust_code, cust_name, cust_city, cust_country
-- Selecting specific columns: cust_code, cust_name, cust_city, cust_country
FROM customer
-- From the table named "customer"
WHERE cust_name LIKE '___l';
-- Where the value in the column cust_name starts with any three characters ('___'), followed by 'l' and nothing else

Explanation:

• SELECT cust_code, cust_name, cust_city, cust_country: This specifies that we want to retrieve specific columns (cust_code, cust_name, cust_city, cust_country) from the table.


• FROM customer: This indicates the table from which we want to retrieve the data. In this case, the table is named "customer".


• WHERE cust_name LIKE '___l': This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the column cust_name starts with any three characters ('___'), followed by 'l' and nothing else should be included in the result set. The '___' symbol represents any three characters, and the 'l' specifies that the next character must be 'l'.

Output:

CUST_CODE  CUST_NAME            CUST_CITY       CUST_COUNTRY
---------- -------------------- --------------- --------------------
C00023     Karl                 London          UK

Relational Algebra Expression:

Relational Algebra Tree:

See our Model Database

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.