Using wild card in list to exclude similar terms
We'd like to exclude common and stop words in the word count query and some terms have a slight variation like "account", "accounts", "account.", etc. Is there a way to use something like a wildcard (such as _ for the LIKE function) in the exclusion list to exclude all variations? There are thousands of words and "account" is just one example.
Query:
SELECT subject
from subject_table
WHERE subject NOT IN ('', 'and', 'for', 'of', 'account')
amazon-redshift
add a comment |
We'd like to exclude common and stop words in the word count query and some terms have a slight variation like "account", "accounts", "account.", etc. Is there a way to use something like a wildcard (such as _ for the LIKE function) in the exclusion list to exclude all variations? There are thousands of words and "account" is just one example.
Query:
SELECT subject
from subject_table
WHERE subject NOT IN ('', 'and', 'for', 'of', 'account')
amazon-redshift
postgres 8 <> redshift - redshift was a fork of an early postgres but has had many updates over that version and has its own architecture. please remove postgres tag and update your title accordingly.
– Jon Scott
Nov 22 '18 at 7:53
add a comment |
We'd like to exclude common and stop words in the word count query and some terms have a slight variation like "account", "accounts", "account.", etc. Is there a way to use something like a wildcard (such as _ for the LIKE function) in the exclusion list to exclude all variations? There are thousands of words and "account" is just one example.
Query:
SELECT subject
from subject_table
WHERE subject NOT IN ('', 'and', 'for', 'of', 'account')
amazon-redshift
We'd like to exclude common and stop words in the word count query and some terms have a slight variation like "account", "accounts", "account.", etc. Is there a way to use something like a wildcard (such as _ for the LIKE function) in the exclusion list to exclude all variations? There are thousands of words and "account" is just one example.
Query:
SELECT subject
from subject_table
WHERE subject NOT IN ('', 'and', 'for', 'of', 'account')
amazon-redshift
amazon-redshift
edited Nov 22 '18 at 15:45
klin
56.8k54778
56.8k54778
asked Nov 22 '18 at 5:17
Leo JonesLeo Jones
577
577
postgres 8 <> redshift - redshift was a fork of an early postgres but has had many updates over that version and has its own architecture. please remove postgres tag and update your title accordingly.
– Jon Scott
Nov 22 '18 at 7:53
add a comment |
postgres 8 <> redshift - redshift was a fork of an early postgres but has had many updates over that version and has its own architecture. please remove postgres tag and update your title accordingly.
– Jon Scott
Nov 22 '18 at 7:53
postgres 8 <> redshift - redshift was a fork of an early postgres but has had many updates over that version and has its own architecture. please remove postgres tag and update your title accordingly.
– Jon Scott
Nov 22 '18 at 7:53
postgres 8 <> redshift - redshift was a fork of an early postgres but has had many updates over that version and has its own architecture. please remove postgres tag and update your title accordingly.
– Jon Scott
Nov 22 '18 at 7:53
add a comment |
1 Answer
1
active
oldest
votes
Try using [REGEXP_INSTR][1]
.
SELECT subject
from subject_table
WHERE REGEXP_INSTR(subject, '(and|for|of|account.*)') = 0
Brilliant! Works like a charm! Thank you, @Joe Harris!
– Leo Jones
Nov 27 '18 at 19:38
Hi Joe, do you know how the following terms can be included in the REGEXP_INSTR() statement? [Angle, I'm
– Leo Jones
Nov 27 '18 at 22:02
You comment got cut off but the pipe|
acts as an OR within the regex.WHERE REGEXP_INSTR(subject, '(and|for|of|account.*|this.*|that|the|^other)') = 0
– Joe Harris
Nov 28 '18 at 18:55
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
});
}
});
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%2f53424291%2fusing-wild-card-in-list-to-exclude-similar-terms%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Try using [REGEXP_INSTR][1]
.
SELECT subject
from subject_table
WHERE REGEXP_INSTR(subject, '(and|for|of|account.*)') = 0
Brilliant! Works like a charm! Thank you, @Joe Harris!
– Leo Jones
Nov 27 '18 at 19:38
Hi Joe, do you know how the following terms can be included in the REGEXP_INSTR() statement? [Angle, I'm
– Leo Jones
Nov 27 '18 at 22:02
You comment got cut off but the pipe|
acts as an OR within the regex.WHERE REGEXP_INSTR(subject, '(and|for|of|account.*|this.*|that|the|^other)') = 0
– Joe Harris
Nov 28 '18 at 18:55
add a comment |
Try using [REGEXP_INSTR][1]
.
SELECT subject
from subject_table
WHERE REGEXP_INSTR(subject, '(and|for|of|account.*)') = 0
Brilliant! Works like a charm! Thank you, @Joe Harris!
– Leo Jones
Nov 27 '18 at 19:38
Hi Joe, do you know how the following terms can be included in the REGEXP_INSTR() statement? [Angle, I'm
– Leo Jones
Nov 27 '18 at 22:02
You comment got cut off but the pipe|
acts as an OR within the regex.WHERE REGEXP_INSTR(subject, '(and|for|of|account.*|this.*|that|the|^other)') = 0
– Joe Harris
Nov 28 '18 at 18:55
add a comment |
Try using [REGEXP_INSTR][1]
.
SELECT subject
from subject_table
WHERE REGEXP_INSTR(subject, '(and|for|of|account.*)') = 0
Try using [REGEXP_INSTR][1]
.
SELECT subject
from subject_table
WHERE REGEXP_INSTR(subject, '(and|for|of|account.*)') = 0
answered Nov 26 '18 at 15:20
Joe HarrisJoe Harris
7,13112841
7,13112841
Brilliant! Works like a charm! Thank you, @Joe Harris!
– Leo Jones
Nov 27 '18 at 19:38
Hi Joe, do you know how the following terms can be included in the REGEXP_INSTR() statement? [Angle, I'm
– Leo Jones
Nov 27 '18 at 22:02
You comment got cut off but the pipe|
acts as an OR within the regex.WHERE REGEXP_INSTR(subject, '(and|for|of|account.*|this.*|that|the|^other)') = 0
– Joe Harris
Nov 28 '18 at 18:55
add a comment |
Brilliant! Works like a charm! Thank you, @Joe Harris!
– Leo Jones
Nov 27 '18 at 19:38
Hi Joe, do you know how the following terms can be included in the REGEXP_INSTR() statement? [Angle, I'm
– Leo Jones
Nov 27 '18 at 22:02
You comment got cut off but the pipe|
acts as an OR within the regex.WHERE REGEXP_INSTR(subject, '(and|for|of|account.*|this.*|that|the|^other)') = 0
– Joe Harris
Nov 28 '18 at 18:55
Brilliant! Works like a charm! Thank you, @Joe Harris!
– Leo Jones
Nov 27 '18 at 19:38
Brilliant! Works like a charm! Thank you, @Joe Harris!
– Leo Jones
Nov 27 '18 at 19:38
Hi Joe, do you know how the following terms can be included in the REGEXP_INSTR() statement? [Angle, I'm
– Leo Jones
Nov 27 '18 at 22:02
Hi Joe, do you know how the following terms can be included in the REGEXP_INSTR() statement? [Angle, I'm
– Leo Jones
Nov 27 '18 at 22:02
You comment got cut off but the pipe
|
acts as an OR within the regex. WHERE REGEXP_INSTR(subject, '(and|for|of|account.*|this.*|that|the|^other)') = 0
– Joe Harris
Nov 28 '18 at 18:55
You comment got cut off but the pipe
|
acts as an OR within the regex. WHERE REGEXP_INSTR(subject, '(and|for|of|account.*|this.*|that|the|^other)') = 0
– Joe Harris
Nov 28 '18 at 18:55
add a comment |
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.
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%2f53424291%2fusing-wild-card-in-list-to-exclude-similar-terms%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
postgres 8 <> redshift - redshift was a fork of an early postgres but has had many updates over that version and has its own architecture. please remove postgres tag and update your title accordingly.
– Jon Scott
Nov 22 '18 at 7:53