w3resource

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 Expression: SQL wildcards underscore ( _ ).

Relational Algebra Tree:

Relational Algebra Tree: SQL wildcards underscore ( _ ).

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 Expression: SQL wildcards underscore ( _ ) multiple characters.

Relational Algebra Tree:

Relational Algebra Tree: SQL wildcards underscore ( _ ) multiple characters.

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 Expression: SQL wildcards underscore ( _ ) for specific length.

Relational Algebra Tree:

Relational Algebra Tree: SQL wildcards underscore ( _ ) for specific length.

See our Model Database

Practice SQL Exercises

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.



Share this Tutorial / Exercise on : Facebook and Twitter