From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Gauthier, Dave Sent: Friday, November 18, 2011 2:56 PM To: pgsql-general@postgresql.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.
