|
Thanks. But, I do not want to convert into upper and show the result.
Example, if I have records as below: id type 1. abcd 2. Abcdef 3. ABcdefg 4. aaadf The below query should report all the above select * from table where type like 'ab%'. It should get all above 3 records. Is there a way the database itself can be made case-insensitive with UTF8 characterset. I tried with character type & collation POSIX, but it did not really help. Thanks and Regards Radha Krishna > From: laurenz.albe@xxxxxxxxxx > To: udayabhanu1984@xxxxxxxxxxx; pgsql-general@xxxxxxxxxxxxxx > Subject: RE: Postgres case insensitive searches > Date: Fri, 28 Jun 2013 12:32:00 +0000 > > bhanu udaya wrote: > > What is the best way of doing case insensitive searches in postgres using Like. > > Table "laurenz.t" > Column | Type | Modifiers > --------+---------+----------- > id | integer | not null > val | text | not null > Indexes: > "t_pkey" PRIMARY KEY, btree (id) > > > CREATE INDEX t_val_ci_ind ON t ((upper(val) text_pattern_ops); > > ANALYZE t; > > EXPLAIN SELECT id FROM t WHERE upper(val) LIKE 'AB%'; > > QUERY PLAN > ------------------------------------------------------------------------------ > Index Scan using t_val_ci_ind on t (cost=0.01..8.28 rows=1 width=4) > Index Cond: ((upper(val) ~>=~ 'AB'::text) AND (upper(val) ~<~ 'AC'::text)) > Filter: (upper(val) ~~ 'AB%'::text) > (3 rows) > > Yours, > Laurenz Albe |
