PostgreSQL LIKE Operator | PostgreSQL Tutorials and Solutions

PostgreSQL LIKE Operator Determines whether a specific character string matches a specified pattern. A pattern can include regular characters and wildcard characters. During pattern matching, regular characters must exactly match the characters specified in the character string.

PostgreSQL LIKE Syntax:

SELECT column1, column2, ..... columnN    
FROM table_name    
WHERE match_expression [ NOT ] LIKE pattern;

Wildcard Character	Description	Example
%	Any string of zero or more characters	WHERE title LIKE ‘%JOHN%’ finds all employees with the word ‘JOHN’ anywhere in the emp ename column.
_ (underscore)	Any single character.	WHERE ename LIKE ‘_ean’ finds all four-letter first names that end with ean (Dean, Sean, and so on).

‘%A’ means first letter starts with A
‘%A%’ anywhere A
‘A%’ means last letter with A
‘_A%’ means second letter must be A
‘%A__’ means 3rd letter from last must be A
‘__V__’ Total 5 letters and middle must be V
‘_____’ Total 5 characters of any letters.

PostgreSQL LIKE Examples:

Below table is used to for the examples we are going to see.

1. Find the employees whose name starts with A

select * from emp where ename like 'A%';

PostgreSQL LIKE Operator

2. Find the employees whose job is having 5 letter.
This can be achieved by LIKE operator or using length() string function.

select * from emp where job like '_____';

select * from emp where length(job)=5 ;

3. Find the employees who’s name ends with ‘D’ in PostgreSQL.

select * from emp where ename like '%D';

4. Find the employees whose name contains L.

select * from emp where ename like '%L%';

5. Find the employees who’s name contains 2nd letter as ‘L’

select * from emp where ename like '_L%';

6. Find the employees who’s name contains A in 3rd position from last.

select * from emp where ename like '%A__';