Using wild card in list to exclude similar terms












0















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')









share|improve this question

























  • 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
















0















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')









share|improve this question

























  • 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














0












0








0








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')









share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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



















  • 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












1 Answer
1






active

oldest

votes


















1














Try using [REGEXP_INSTR][1].



SELECT subject
from subject_table
WHERE REGEXP_INSTR(subject, '(and|for|of|account.*)') = 0





share|improve this answer
























  • 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











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


















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









1














Try using [REGEXP_INSTR][1].



SELECT subject
from subject_table
WHERE REGEXP_INSTR(subject, '(and|for|of|account.*)') = 0





share|improve this answer
























  • 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
















1














Try using [REGEXP_INSTR][1].



SELECT subject
from subject_table
WHERE REGEXP_INSTR(subject, '(and|for|of|account.*)') = 0





share|improve this answer
























  • 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














1












1








1







Try using [REGEXP_INSTR][1].



SELECT subject
from subject_table
WHERE REGEXP_INSTR(subject, '(and|for|of|account.*)') = 0





share|improve this answer













Try using [REGEXP_INSTR][1].



SELECT subject
from subject_table
WHERE REGEXP_INSTR(subject, '(and|for|of|account.*)') = 0






share|improve this answer












share|improve this answer



share|improve this answer










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



















  • 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


















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














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





















































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







Popular posts from this blog

404 Error Contact Form 7 ajax form submitting

How to know if a Active Directory user can login interactively

TypeError: fit_transform() missing 1 required positional argument: 'X'