CODEWITHSUNDEEP
postgresql
thiebo
thiebo

Reputation: 1435

full text search postgresql : how to substitute 2 letters

I have a postgresql database with entries in latin. The letters "u" and "v" can in any text and at any moment be substituted for each other.

So, the word "individue", can also be written "indiuidue" or "indiuidve".

How do I instruct the query (or format the database) so that a search on "individue" also returns the 2 other possibilities?

Thanks,

Upvotes: 1

Views: 334

Answers (2)

Joe Love
Joe Love

Reputation: 5972

There are several ways-- you could create a custom parser for FTS, or you could simply do something like:

create index on base_table 
    using gin( (to_tsvector(replace(upper(column_to_search),'U','V')))

And remember to do the same replace(search_criteria),'U','V') on all searches

Of course you can turn this into a immutable function that will always convert the U's to V's.. and just call that in both places (the index and when searching). That way, you won't have to write out that ugly replace so often...

Upvotes: 1

Colin Beckingham
Colin Beckingham

Reputation: 525

One approach would be to use the "select ... similar to ..." as in:

> createdb test
> psql -d test
psql (9.4.9)
Type "help" for help.

test=# create table mytest(thestring varchar(20));
CREATE TABLE

test=# insert into mytest values('individue');
INSERT 0 1
test=# insert into mytest values('indiuidue');
INSERT 0 1
test=# insert into mytest values('indiuidve');
INSERT 0 1
test=# insert into mytest values('indiuidde');
INSERT 0 1
test=# insert into mytest values('indiiidde');
INSERT 0 1

test=# select * from mytest
test-# ;
 thestring 
-----------
 individue
 indiuidue
 indiuidve
 indiuidde
 indiiidde
(5 rows)

test=# select * from mytest where thestring similar to '%(u|v)%';
 thestring 
-----------
 individue
 indiuidue
 indiuidve
 indiuidde
(4 rows)

If this does not suit your purposes then regular expressions would be able to handle a more complex situation.

Upvotes: 0

Related Questions