From: | "David Johnston" <polobo(at)yahoo(dot)com> |
---|---|
To: | "'Gauthier, Dave'" <dave(dot)gauthier(at)intel(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to use like with a list |
Date: | 2011-11-18 20:12:52 |
Message-ID: | 055801cca62e$7454e950$5cfebbf0$@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Gauthier, Dave
Sent: Friday, November 18, 2011 2:56 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] How to use like with a list
Hi:
How can I search on a csv list of values using "like" where each value is to
be appended with a wildcarded string?
Example:
list = 'jo,mo,do,fo'
I want to pull all names from a table with name like.. 'jol%' or 'mol%'
or'dol%' or 'sol%'
would match "jolly, molly, moleman,dollface, solarboy"
notice they all have the same "l%' wildcarded suffix.
I'm really not that lazy, just trying to keep this in a simgle query in
order to minimize network hits which will reduce overall wallclock
performance. This thing wil be running in a programming loop.
Thanks in Advance !
Do you need a solution for your specific example or something more
generalized?
You can make use of "substing" (on the input) and "split_to_array" (on the
csv list) to efficiently solve your stated problem (possibly with indexes).
Something more general would be to convert:
'jo,mo,do,fo' INTO '^(jo|mo|do|fo)L.*' (upper case "L" for clarity) and then
use "regexp_matches"
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2011-11-18 20:13:33 | Re: How to use like with a list |
Previous Message | Raymond O'Donnell | 2011-11-18 20:07:43 | Re: How to install latest stable postgresql on Debian |