9

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.

peterm
  • 91,357
  • 15
  • 148
  • 157
  • 2
    I would consider to replace *any* occurrence of `SIMILAR TO` with a regular expression match (`~`) or one of the other available operators. I wrote a [comprehensive answer with details on dba.SE](http://dba.stackexchange.com/questions/10694/pattern-matching-with-like-similar-to-or-regular-expressions-in-postgresql/10696#10696). – Erwin Brandstetter Jun 18 '13 at 22:00

2 Answers2

20

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.

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 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. –  Jun 18 '13 at 05:27
  • Just fixed the regexp, I forgot to add the left anchor (`^`) to make sure that the `R` was the *first* character. – Craig Ringer Jun 18 '13 at 05:30
  • @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. – Craig Ringer Jun 18 '13 at 05:31
  • Thanks Craig. I ll do that. –  Jun 18 '13 at 05:59
1

try

SELECT * FROM table WHERE columnName  SIMILAR TO 'R%|r%'
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
Sanjaya Liyanage
  • 4,706
  • 9
  • 36
  • 50
  • Good thought, and that's standard, too.... not that many DBs apparently implement `SIMILAR TO`. – Craig Ringer Jun 18 '13 at 04:49
  • @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. –  Jun 18 '13 at 04:50
  • @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? –  Jun 18 '13 at 05:21
  • @Rinny Finally test this answer. If not I quit :( – Sanjaya Liyanage Jun 18 '13 at 05:36
  • Sorry Sanjaya. It doesn't work :(. I get empty result set. Thanks for help so far. –  Jun 18 '13 at 06:10
  • @CraigRinger thanks Craig. I didn't test the answer coz not using posgres. I was wondering why it didn't work. – Sanjaya Liyanage Jun 18 '13 at 07:41
  • 1
    @SanjayaLiyanage http://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%'` – Craig Ringer Jun 18 '13 at 08:45
  • @CraigRinger thanks Craig for helpful ideas and the edit. I learnt things from you. – Sanjaya Liyanage Jun 18 '13 at 08:54