CODEWITHSUNDEEP
postgresql
user2462203
user2462203

Reputation:

Wildcard search in postgresql

In postgresql, I have mangaged to add wildcard pattern (*) to the query using SIMILAR TO option. So my query will be, 

SELECT * FROM table WHERE columnName SIMILAR TO 'R*'

This query would return all entities starting from 'R' and not 'r'. I want to make it case insensitive.

Upvotes: 11

Views: 23919

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324325

Use ILIKE:

SELECT * FROM table WHERE columnName  ILIKE 'R%';

or a case-insensitive regular expression:

SELECT * FROM table WHERE columnName ~* '^R.*';

Both are PostgreSQL extensions. Sanjaya has already outlined the standards-compliant approaches - filtering both sides with lower(...) or using a two-branch SIMILAR TO expression.

SIMILAR TO is less than lovely and best avoided. See this earlier answer.

You could write:

SELECT * FROM table WHERE columnName SIMILAR TO '(R|r)%'

but I don't particularly recommend using SIMILAR TO.

Upvotes: 24

Sanjaya Liyanage
Sanjaya Liyanage

Reputation: 4746

try

SELECT * FROM table WHERE columnName  SIMILAR TO 'R%|r%'

Upvotes: 1

Related Questions