case insensitive in regex in postgresql query

Question

I want to search for a list of name through records of another table i want that my regex be case insensitive, but I can not make it work!

SELECT id
  FROM "regexfreeFlickrFullInfo"
  where tags ~ 'tower\s?\*?bridge'  or title ~ 'tower\s?\*?bridge' or descriptio ~ 'tower\s?\*?bridge'  order by id asc;

here is my query, I have tested by i, but it did not work!

score 66 · Accepted Answer · answered Sep 01 '16 at 14:10

66

You must use ~* instead of ~.

answered Sep 01 '16 at 14:10

redneb

21,794
6
42
54

score 13 · Answer 2 · answered Jan 04 '22 at 08:14

If you want to support case sensitive and insensitive queries without changing the query itself, you may change the behavior of the operator with the Regular Expression Metasyntax. They can override the case-sensitivity behavior implied by a regex operator..

tags ~ 'Case Sensitive'
tags ~ '(?i)case insensitive'
tags ~* 'case insensitive'
tags ~* '(?c)Case Sensitive'

case insensitive in regex in postgresql query

2 Answers2