Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-8.x)/ FAQs/ How Do I Ignore Case When Using LIKE for Fuzzy Search?
Updated on 2025-05-29 GMT+08:00

How Do I Ignore Case When Using LIKE for Fuzzy Search?

Answer: You can change LIKE to ILIKE or use the upper or lower function.

Example:
-- Perform pre-operations: creating tables and inserting data.
gaussdb=# CREATE TABLE tbl_test1(c1 varchar); 
gaussdb=# INSERT INTO tbl_test1 VALUES ('EEE'),('ABC'),('abc'),('aabccd');

-- When LIKE is used for fuzzy query, all uppercase character strings are filtered out, which does not meet the expectation.
gaussdb=# SELECT * FROM tbl_test1 WHERE c1 LIKE 'ab%';
 c1  
-----
 abc
(1 row)

-- Use ILIKE for fuzzy search.
gaussdb=# SELECT * FROM tbl_test1 WHERE c1 ILIKE 'ab%';
 c1  
-----
 ABC
 abc
(2 rows)

-- Use the upper() function.
gaussdb=# SELECT * FROM tbl_test1 WHERE upper(c1) LIKE 'AB%';
 c1  
-----
 ABC
 abc
(2 rows)

-- Delete.
gaussdb=# DROP TABLE tbl_test1;