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!

redneb · Accepted Answer · 2016-09-01 14:10:54Z

74

You must use ~* instead of ~.

answered Sep 1, 2016 at 14:10

redneb

24.4k6 gold badges50 silver badges57 bronze badges

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

Comments

fracz · Accepted Answer · 2022-01-04 08:14:46Z

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'

Collectives™ on Stack Overflow

case insensitive in regex in postgresql query

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related