Shulou(Shulou.com)05/31 Report--

This article focuses on "what are the Putting multiple LIKE patterns into an array features in PostgreSQL". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what are the Putting multiple LIKE patterns into an array features in PostgreSQL"?

LIKE is used for fuzzy queries, such as select from tablename where column like'% value%';. If multiple fuzzy matches are required, multiple like clauses are required, such as select from tablename where column like'% value1%' or column like'% value2%' or column like'% value3%' or select * from tablename where column like'% value1%' and column like'% value2%' and column like'% value3%'. PG provides ANY/ALL in conjunction with ARRAY to simplify SQL:

Select from tablename where column like'% value1%' or column like'% value2%' or column like'% value3%'

->

Select from tablename where column like ANY (ARRAY ['% value1%','%value2%','%value3%'])

Select from tablename where column like'% value1%' and column like'% value2%' and column like'% value3%'

->

Select from tablename where column like ALL (ARRAY ['% value1%','%value2%','%value3%'])

Test script

[local]: 5432 pg12@testdb=# create table t_like (id int,c1 varchar (20)); CREATE TABLETime: 160.410 ms [local]: 5432 pg12@testdb=# [local]: 5432 pg12@testdb=# insert into t_like (id,c1) values; INSERT 0 1Time: 14.815 ms [local]: 5432 pg12@testdb=# insert into t_like (id,c1) values INSERT 0 1Time: 1.423 ms [local]: 5432 pg12@testdb=# insert into t_like (id,c1) values; INSERT 0 1Time: 1.486 ms [local]: 5432 pg12@testdb=# select * from t_like where C1 like ANY (ARRAY ['% test%','% Test%']) Id | C1-+-1 | asdfafetestsdfasdf 2 | asdfafe Test sdfasdf 3 | astestfe Test sdfasdf (3 rows) Time: 48.319 ms [local]: 5432 pg12@testdb=# select * from t_like where C1 like ALL (ARRAY ['% test%','% Test%']) Id | C1-+-3 | astestfe test sdfasdf (1 row) Time: 2.463 ms [local]: 5432 pg12@testdb=# so far, I believe you have a better understanding of "what are the Putting multiple LIKE patterns into an array features in PostgreSQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.