13

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.

Improve this question
1

2 Answers 2

Reset to default
26

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks. I have tried using ilike too. The problem is, I have a scenario where the user can either enter the exact entity name or use a wildcard pattern. so when the user enters the exact entity name, it also displays everything related to the exact entity name. i dont want that to happen.
Just fixed the regexp, I forgot to add the left anchor (^) to make sure that the R was the first character.
@Rinny You'd have to translate your wildcard syntax into LIKE patterns. You should really do this anyway, no matter what you're using, to avoid confusing the user when they enter text only to get a weird database error if their pattern matching syntax is invalid. Unless you're going to force the user to enter valid POSIX regular expressions, extended regex, LIKE patterns, or something directly... not user friendly. So I'd parse their input and from that produce a new expression to insert into the SQL.
Thanks Craig. I ll do that.
1

try

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

8 Comments

Good thought, and that's standard, too.... not that many DBs apparently implement SIMILAR TO.
@Sanjaya. Thanks. But it doesnt work. and in my query the search text would be from a user. so the user can either give r* or the entire entity name also.
@Sanjaya. Thanks. This works. But, now if i give the query that would exactly match the entityname like 'R1', it displays 'R11','R12','TR1','TR123', etc. What should i do?
@Rinny Finally test this answer. If not I quit :(
@SanjayaLiyanage sqlfiddle.com is very useful for that. When an answer is un-tested please say so, though, it helps avoid confusion. Your latest edit is invalid, by the way; LIKE doesn't use the same expression syntax as SIMILAR TO. The original didn't work in testing either, actually, though I thought it should I misremembered the little-used SIMILAR TO syntax. The docs say: Also like LIKE, SIMILAR TO uses _ and % as wildcard characters denoting any single character and any string, respectively so you would've had to write 'R%|r%'
|