postgres regex query to match multiple strings pattern
0
A postgres query selects the elements which have a name column containing any strings from an array:
select "elements".* from "elements" where "elements"."name" ~* 'hap|bir'
This works well.
Now,
what is the regex pattern to select the elements only if the column contains every strings of the array (in no particular order)?
regex postgresql
asked Nov 21 '18 at 22:50
François RomainFrançois Romain
4,101125486
add a comment |
0
A postgres query selects the elements which have a name column containing any strings from an array:
select "elements".* from "elements" where "elements"."name" ~* 'hap|bir'
This works well.
Now,
what is the regex pattern to select the elements only if the column contains every strings of the array (in no particular order)?
regex postgresql
asked Nov 21 '18 at 22:50
François RomainFrançois Romain
4,101125486
add a comment |
0
0
0
A postgres query selects the elements which have a name column containing any strings from an array:
select "elements".* from "elements" where "elements"."name" ~* 'hap|bir'
This works well.
Now,
what is the regex pattern to select the elements only if the column contains every strings of the array (in no particular order)?
regex postgresql
asked Nov 21 '18 at 22:50
François RomainFrançois Romain
4,101125486
A postgres query selects the elements which have a name column containing any strings from an array:
select "elements".* from "elements" where "elements"."name" ~* 'hap|bir'
This works well.
Now,
what is the regex pattern to select the elements only if the column contains every strings of the array (in no particular order)?
regex postgresql
regex postgresql
asked Nov 21 '18 at 22:50
François RomainFrançois Romain
4,101125486
asked Nov 21 '18 at 22:50
François RomainFrançois Romain
4,101125486
edited Nov 22 '18 at 19:59
François Romain
asked Nov 21 '18 at 22:50
François RomainFrançois Romain
4,101125486
asked Nov 21 '18 at 22:50
François RomainFrançois Romain
4,101125486
asked Nov 21 '18 at 22:50
François RomainFrançois Romain
4,101125486
4,101125486
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
1
Your query produces the same result as the following non-regex LIKE expression using an array
SELECT *
FROM elements
WHERE lower(name) LIKE ANY(ARRAY['%hap%', '%bir%']);
So, to select from elements only if the column contains every strings of the array, you change it from ANY to ALL
SELECT *
FROM elements
WHERE lower(name) LIKE ALL(ARRAY['%hap%', '%bir%']);
Demo
answered Nov 22 '18 at 5:29
Kaushik NayakKaushik Nayak
18.4k41230
nope, it's not actually equivalent to pass two regexps to each string in the set than to pass only one. Regexp matching in a database is difficult to implement when you allow head wilcard expressions (you need a partial match search index, and you have to pass it through all the regexps in the array, making the search sequential, and used n times, when you have an array of different regexps) A single regexp decides in just one pass through the search string, and as such, is more efficient than having n regexps to match.
– Luis Colorado
Nov 22 '18 at 7:13
@LuisColorado : Pardon my ignorance. I couldn't understand clearly what you are saying. If you have an answer feel free to put one.Thanks!
– Kaushik Nayak
Nov 22 '18 at 7:17
I'm sorry, you have said Your query is actually equivalent to the following non-regex LIKE expression using an array, and it is not. Both queries require passing over the data trying to match a string (the candidate string) through a regexp matcher, but you feed your regexp in one array of n-regex, and the question uses only one. there's a n-to-1 pass difference between both queries. To be exactly equal, you need to construct a single regex that matches the ones in your array, and that's what the|operator does in normal regex. I dont write an answer...
– Luis Colorado
Nov 22 '18 at 7:23
... because this is not an answer to the original question. Should I have an answer to the original question, I had written one.
– Luis Colorado
Nov 22 '18 at 7:25
@LuisColorado : Ah! ok. When I meant equivalent, I said it gives the same results. I didn't worry about the implementation part of it. I don't know how Postgres implements the first query, so I can't say anything about it if it does in n pass or 1 pass.
– Kaushik Nayak
Nov 22 '18 at 7:28
|
show 5 more comments
0
Here is a regex pattern to select the elements only if the column contains every strings of the array (in no particular order):
select "elements".* from "elements" where "elements"."name" ~* '(?=.*?(hap))(?=.*?(bir))'
from here.
answered Nov 22 '18 at 19:59
François RomainFrançois Romain
4,101125486
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53421535%2fpostgres-regex-query-to-match-multiple-strings-pattern%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Your query produces the same result as the following non-regex LIKE expression using an array
SELECT *
FROM elements
WHERE lower(name) LIKE ANY(ARRAY['%hap%', '%bir%']);
So, to select from elements only if the column contains every strings of the array, you change it from ANY to ALL
SELECT *
FROM elements
WHERE lower(name) LIKE ALL(ARRAY['%hap%', '%bir%']);
Demo
answered Nov 22 '18 at 5:29
Kaushik NayakKaushik Nayak
18.4k41230
nope, it's not actually equivalent to pass two regexps to each string in the set than to pass only one. Regexp matching in a database is difficult to implement when you allow head wilcard expressions (you need a partial match search index, and you have to pass it through all the regexps in the array, making the search sequential, and used n times, when you have an array of different regexps) A single regexp decides in just one pass through the search string, and as such, is more efficient than having n regexps to match.
– Luis Colorado
Nov 22 '18 at 7:13
@LuisColorado : Pardon my ignorance. I couldn't understand clearly what you are saying. If you have an answer feel free to put one.Thanks!
– Kaushik Nayak
Nov 22 '18 at 7:17
I'm sorry, you have said Your query is actually equivalent to the following non-regex LIKE expression using an array, and it is not. Both queries require passing over the data trying to match a string (the candidate string) through a regexp matcher, but you feed your regexp in one array of n-regex, and the question uses only one. there's a n-to-1 pass difference between both queries. To be exactly equal, you need to construct a single regex that matches the ones in your array, and that's what the|operator does in normal regex. I dont write an answer...
– Luis Colorado
Nov 22 '18 at 7:23
... because this is not an answer to the original question. Should I have an answer to the original question, I had written one.
– Luis Colorado
Nov 22 '18 at 7:25
@LuisColorado : Ah! ok. When I meant equivalent, I said it gives the same results. I didn't worry about the implementation part of it. I don't know how Postgres implements the first query, so I can't say anything about it if it does in n pass or 1 pass.
– Kaushik Nayak
Nov 22 '18 at 7:28
|
show 5 more comments
1
Your query produces the same result as the following non-regex LIKE expression using an array
SELECT *
FROM elements
WHERE lower(name) LIKE ANY(ARRAY['%hap%', '%bir%']);
So, to select from elements only if the column contains every strings of the array, you change it from ANY to ALL
SELECT *
FROM elements
WHERE lower(name) LIKE ALL(ARRAY['%hap%', '%bir%']);
Demo
answered Nov 22 '18 at 5:29
Kaushik NayakKaushik Nayak
18.4k41230
nope, it's not actually equivalent to pass two regexps to each string in the set than to pass only one. Regexp matching in a database is difficult to implement when you allow head wilcard expressions (you need a partial match search index, and you have to pass it through all the regexps in the array, making the search sequential, and used n times, when you have an array of different regexps) A single regexp decides in just one pass through the search string, and as such, is more efficient than having n regexps to match.
– Luis Colorado
Nov 22 '18 at 7:13
@LuisColorado : Pardon my ignorance. I couldn't understand clearly what you are saying. If you have an answer feel free to put one.Thanks!
– Kaushik Nayak
Nov 22 '18 at 7:17
I'm sorry, you have said Your query is actually equivalent to the following non-regex LIKE expression using an array, and it is not. Both queries require passing over the data trying to match a string (the candidate string) through a regexp matcher, but you feed your regexp in one array of n-regex, and the question uses only one. there's a n-to-1 pass difference between both queries. To be exactly equal, you need to construct a single regex that matches the ones in your array, and that's what the|operator does in normal regex. I dont write an answer...
– Luis Colorado
Nov 22 '18 at 7:23
... because this is not an answer to the original question. Should I have an answer to the original question, I had written one.
– Luis Colorado
Nov 22 '18 at 7:25
@LuisColorado : Ah! ok. When I meant equivalent, I said it gives the same results. I didn't worry about the implementation part of it. I don't know how Postgres implements the first query, so I can't say anything about it if it does in n pass or 1 pass.
– Kaushik Nayak
Nov 22 '18 at 7:28
|
show 5 more comments
1
1
1
Your query produces the same result as the following non-regex LIKE expression using an array
SELECT *
FROM elements
WHERE lower(name) LIKE ANY(ARRAY['%hap%', '%bir%']);
So, to select from elements only if the column contains every strings of the array, you change it from ANY to ALL
SELECT *
FROM elements
WHERE lower(name) LIKE ALL(ARRAY['%hap%', '%bir%']);
Demo
answered Nov 22 '18 at 5:29
Kaushik NayakKaushik Nayak
18.4k41230
Your query produces the same result as the following non-regex LIKE expression using an array
SELECT *
FROM elements
WHERE lower(name) LIKE ANY(ARRAY['%hap%', '%bir%']);
So, to select from elements only if the column contains every strings of the array, you change it from ANY to ALL
SELECT *
FROM elements
WHERE lower(name) LIKE ALL(ARRAY['%hap%', '%bir%']);
Demo
answered Nov 22 '18 at 5:29
Kaushik NayakKaushik Nayak
18.4k41230
edited Nov 22 '18 at 8:08
answered Nov 22 '18 at 5:29
Kaushik NayakKaushik Nayak
18.4k41230
answered Nov 22 '18 at 5:29
Kaushik NayakKaushik Nayak
18.4k41230
answered Nov 22 '18 at 5:29
Kaushik NayakKaushik Nayak
18.4k41230
18.4k41230
nope, it's not actually equivalent to pass two regexps to each string in the set than to pass only one. Regexp matching in a database is difficult to implement when you allow head wilcard expressions (you need a partial match search index, and you have to pass it through all the regexps in the array, making the search sequential, and used n times, when you have an array of different regexps) A single regexp decides in just one pass through the search string, and as such, is more efficient than having n regexps to match.
– Luis Colorado
Nov 22 '18 at 7:13
@LuisColorado : Pardon my ignorance. I couldn't understand clearly what you are saying. If you have an answer feel free to put one.Thanks!
– Kaushik Nayak
Nov 22 '18 at 7:17
I'm sorry, you have said Your query is actually equivalent to the following non-regex LIKE expression using an array, and it is not. Both queries require passing over the data trying to match a string (the candidate string) through a regexp matcher, but you feed your regexp in one array of n-regex, and the question uses only one. there's a n-to-1 pass difference between both queries. To be exactly equal, you need to construct a single regex that matches the ones in your array, and that's what the|operator does in normal regex. I dont write an answer...
– Luis Colorado
Nov 22 '18 at 7:23
... because this is not an answer to the original question. Should I have an answer to the original question, I had written one.
– Luis Colorado
Nov 22 '18 at 7:25
@LuisColorado : Ah! ok. When I meant equivalent, I said it gives the same results. I didn't worry about the implementation part of it. I don't know how Postgres implements the first query, so I can't say anything about it if it does in n pass or 1 pass.
– Kaushik Nayak
Nov 22 '18 at 7:28
|
show 5 more comments
nope, it's not actually equivalent to pass two regexps to each string in the set than to pass only one. Regexp matching in a database is difficult to implement when you allow head wilcard expressions (you need a partial match search index, and you have to pass it through all the regexps in the array, making the search sequential, and used n times, when you have an array of different regexps) A single regexp decides in just one pass through the search string, and as such, is more efficient than having n regexps to match.
– Luis Colorado
Nov 22 '18 at 7:13
@LuisColorado : Pardon my ignorance. I couldn't understand clearly what you are saying. If you have an answer feel free to put one.Thanks!
– Kaushik Nayak
Nov 22 '18 at 7:17
I'm sorry, you have said Your query is actually equivalent to the following non-regex LIKE expression using an array, and it is not. Both queries require passing over the data trying to match a string (the candidate string) through a regexp matcher, but you feed your regexp in one array of n-regex, and the question uses only one. there's a n-to-1 pass difference between both queries. To be exactly equal, you need to construct a single regex that matches the ones in your array, and that's what the|operator does in normal regex. I dont write an answer...
– Luis Colorado
Nov 22 '18 at 7:23
... because this is not an answer to the original question. Should I have an answer to the original question, I had written one.
– Luis Colorado
Nov 22 '18 at 7:25
@LuisColorado : Ah! ok. When I meant equivalent, I said it gives the same results. I didn't worry about the implementation part of it. I don't know how Postgres implements the first query, so I can't say anything about it if it does in n pass or 1 pass.
– Kaushik Nayak
Nov 22 '18 at 7:28
nope, it's not actually equivalent to pass two regexps to each string in the set than to pass only one. Regexp matching in a database is difficult to implement when you allow head wilcard expressions (you need a partial match search index, and you have to pass it through all the regexps in the array, making the search sequential, and used n times, when you have an array of different regexps) A single regexp decides in just one pass through the search string, and as such, is more efficient than having n regexps to match.
– Luis Colorado
Nov 22 '18 at 7:13
nope, it's not actually equivalent to pass two regexps to each string in the set than to pass only one. Regexp matching in a database is difficult to implement when you allow head wilcard expressions (you need a partial match search index, and you have to pass it through all the regexps in the array, making the search sequential, and used n times, when you have an array of different regexps) A single regexp decides in just one pass through the search string, and as such, is more efficient than having n regexps to match.
– Luis Colorado
Nov 22 '18 at 7:13
@LuisColorado : Pardon my ignorance. I couldn't understand clearly what you are saying. If you have an answer feel free to put one.Thanks!
– Kaushik Nayak
Nov 22 '18 at 7:17
@LuisColorado : Pardon my ignorance. I couldn't understand clearly what you are saying. If you have an answer feel free to put one.Thanks!
– Kaushik Nayak
Nov 22 '18 at 7:17
I'm sorry, you have said Your query is actually equivalent to the following non-regex LIKE expression using an array, and it is not. Both queries require passing over the data trying to match a string (the candidate string) through a regexp matcher, but you feed your regexp in one array of n-regex, and the question uses only one. there's a n-to-1 pass difference between both queries. To be exactly equal, you need to construct a single regex that matches the ones in your array, and that's what the
| operator does in normal regex. I dont write an answer...– Luis Colorado
Nov 22 '18 at 7:23
I'm sorry, you have said Your query is actually equivalent to the following non-regex LIKE expression using an array, and it is not. Both queries require passing over the data trying to match a string (the candidate string) through a regexp matcher, but you feed your regexp in one array of n-regex, and the question uses only one. there's a n-to-1 pass difference between both queries. To be exactly equal, you need to construct a single regex that matches the ones in your array, and that's what the
| operator does in normal regex. I dont write an answer...– Luis Colorado
Nov 22 '18 at 7:23
... because this is not an answer to the original question. Should I have an answer to the original question, I had written one.
– Luis Colorado
Nov 22 '18 at 7:25
... because this is not an answer to the original question. Should I have an answer to the original question, I had written one.
– Luis Colorado
Nov 22 '18 at 7:25
@LuisColorado : Ah! ok. When I meant equivalent, I said it gives the same results. I didn't worry about the implementation part of it. I don't know how Postgres implements the first query, so I can't say anything about it if it does in n pass or 1 pass.
– Kaushik Nayak
Nov 22 '18 at 7:28
@LuisColorado : Ah! ok. When I meant equivalent, I said it gives the same results. I didn't worry about the implementation part of it. I don't know how Postgres implements the first query, so I can't say anything about it if it does in n pass or 1 pass.
– Kaushik Nayak
Nov 22 '18 at 7:28
|
show 5 more comments
0
Here is a regex pattern to select the elements only if the column contains every strings of the array (in no particular order):
select "elements".* from "elements" where "elements"."name" ~* '(?=.*?(hap))(?=.*?(bir))'
from here.
answered Nov 22 '18 at 19:59
François RomainFrançois Romain
4,101125486
add a comment |
0
Here is a regex pattern to select the elements only if the column contains every strings of the array (in no particular order):
select "elements".* from "elements" where "elements"."name" ~* '(?=.*?(hap))(?=.*?(bir))'
from here.
answered Nov 22 '18 at 19:59
François RomainFrançois Romain
4,101125486
add a comment |
0
0
0
Here is a regex pattern to select the elements only if the column contains every strings of the array (in no particular order):
select "elements".* from "elements" where "elements"."name" ~* '(?=.*?(hap))(?=.*?(bir))'
from here.
answered Nov 22 '18 at 19:59
François RomainFrançois Romain
4,101125486
Here is a regex pattern to select the elements only if the column contains every strings of the array (in no particular order):
select "elements".* from "elements" where "elements"."name" ~* '(?=.*?(hap))(?=.*?(bir))'
from here.
answered Nov 22 '18 at 19:59
François RomainFrançois Romain
4,101125486
answered Nov 22 '18 at 19:59
François RomainFrançois Romain
4,101125486
answered Nov 22 '18 at 19:59
François RomainFrançois Romain
4,101125486
answered Nov 22 '18 at 19:59
François RomainFrançois Romain
4,101125486
4,101125486
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53421535%2fpostgres-regex-query-to-match-multiple-strings-pattern%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
