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.3k7 gold badges46 silver badges55 bronze badges

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

Comments

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

16

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'

answered Jan 4, 2022 at 8:14

$fracz's user avatar$

fracz

21.4k18 gold badges109 silver badges154 bronze badges

Collectives™ on Stack Overflow

case insensitive in regex in postgresql query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related