SQL Like Statement with CHAR and VARCHAR: Top Differences

The content discusses how to use the Like statement with Char and Varchar data types. This statement allows users to retrieve rows based on non-specific criteria, such as retrieving rows with only a partial name match. It provides an example of using the Like statement to obtain relevant rows when part of a person’s name is known, but not the complete name.

Table of contents

1. LIKE on CHAR field
2. LIKE on VARCHAR Field

LIKE on CHAR field

NAME CHAR(20)

If the name is “SRINI,” it takes up only five bytes out of the total allocated 20 bytes. The remaining bytes are filled with spaces.

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

It won’t return any rows because the name ending with ‘I’ is not found in the table due to trailing spaces.

LIKE on VARCHAR Field

NAME VARCHAR(20)

If the name is “SRINI”, it only takes up five bytes of data. The rest of the bytes are automatically removed.

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

Here, the output is “SRINI.” This is the main difference in how LIKE works for 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 empty because it is a 20-character field and the last character is a space. However, there is no problem with FULL_NAME, which is also a 20-character field. Therefore, both rows are displayed.

References

• The Ultimate Oracle SQL Course from Scratch