PostgreSQL multiple substring search

Question

I want to fetch substrings from a GET-string in Javascript to pass them to a query on PostgreSQL. I've tried already with PostgreSQL full text search, but ended up with the problem, that full text search only allows prefix matching (Already described here).

So I'm looking to use the 'ILIKE ALL(array)' with an array of substrings with wildcards.

SELECT * from parcels WHERE doc ilike ALL(array['%substr1%','%substr2%', '%substr3%']);

I split up my GET-string in Javascript like this.

var str = (GET()['search']).split ('+');

which gives me already an array of substrings. How can I add a leading and trailing % to each substring with regEx? Or is there a better way to archieve this?

trincot · Accepted Answer · 2017-08-13 17:44:16Z

0

In JavaScript you can use map for that:

var str = (GET()['search']).split('+').map(word => '%' + word + '%');

In ES5 compatible code:

var str = (GET()['search']).split('+').map(function (word) { return '%' + word + '%'; });

answered Aug 13, 2017 at 17:44

trincot

357k38 gold badges282 silver badges338 bronze badges

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

2 Comments

geom Over a year ago

Works like a charm. I just had to encode the % to %25 and add ' as %27, so that my query could be send via GET. so: var str = (GET()['search']).split('+').map(function (word) { return '%27%25' + word + '%25%27'; }); does the job. My Where-clause then is called via: where: "doc ilike all(array["+str+"])",

trincot Over a year ago

Ah, yes, you could in fact use return encodeURIComponent("'%" + word + "%'"), that way you leave the encoding to JavaScript, which will also escape special characters if they occur in the word itself. Although I notice that a quote is not regarded as special, so I am not sure why it would be a problem in your case.

Collectives™ on Stack Overflow

PostgreSQL multiple substring search

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related