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
FROM customer
WHERE cust_name LIKE 'R_m%';
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
FROM customer
WHERE cust_name LIKE '___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
FROM customer
WHERE cust_name LIKE '___l';
Output:
CUST_CODE CUST_NAME CUST_CITY CUST_COUNTRY ---------- -------------------- --------------- -------------------- C00023 Karl London UK
Relational Algebra Expression:
Relational Algebra Tree:
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics