How to Use SQL LIKE on CHAR and VARCHAR

Here’s a way to use the Like statement on Char and Varchar. It can use to get rows of non-specific criteria. For instance, you know the name of a person but not the complete name. Then, you can use it to get the rows.

In this Page

1. The Like statement on CHAR
2. The Like statement on VARCHAR

The Like statement on CHAR

NAME CHAR(20)

Suppose, if the name is SRINI, it occupies only five bytes out of 20 allocated. And, the remaining bytes occupy spaces.

SELECT * FROM HR WHERE NAME LIKE '%I';

It will not result in any rows since the name ending with ‘I’ is not found in the table since the last characters are spaces.

The Like statement on VARCHAR

NAME VARCHAR(20)

If the name is SRINI, it occupies only five bytes of data. And the remaining bytes trim automatically.

SELECT * FROM HR WHERE NAME LIKE '%I';

Here, you will get the output SRINI. So this is the essential difference between how LIKE works on CHAR and VARCHAR.

Data in the HR table

Insert values

INSERT INTO HR VALUES (122221, 'VENKAT', 'VENKAT RAO');

INSERT INTO HR VALUES (101122, 'SRINI', 'SRINI RAO');

Data in the Table

Here are two columns SHORT_NAME and FULL_NAME columns. SHORT_NAME is CHAR(20), and FULL_NAME is VARCHAR(20).

SQL Queries

SELECT * FROM HR WHERE SHORT_NAME LIKE '%I';
SELECT * FROM HR WHERE FULL_NAME LIKE '%O';

Output

The query result for SHORT_NAME is NULL since it is CHAR(20) and the last byte is space. There is no issue with FULL_NAME since it is VARCHAR(20) (So both rows are displayed).

References

• The Ultimate Oracle SQL Course from Scratch

Related