Ygthkb

Stuck to wireframe

Renting a 2CV in France

Solving the linear first order differential equation?

my cron command doesn’t work

How do I narratively explain how in-game circumstances do not mechanically allow a PC to instantly kill an NPC?

Is the fingering of thirds flexible or do I have to follow the rules?

Why did Luke use his left hand to shoot?

Fraction within another fraction

Feature to polygon in multiple folders

Boss asked me to sign a resignation paper without a date on it along with my new contract

Why is it that Bernie Sanders is always called a "socialist"?

Why is "rm -r" unable to delete this folder?

How to not let the Identify spell spoil everything?

What does an unprocessed RAW file look like?

XOR-free sets: Maximum density?

How can I automatically launch GPSD on startup?

How to deal with an underperforming subordinate?

I have trouble understanding this fallacy: "If A, then B. Therefore if not-B, then not-A."

Rigorous justification for non-relativistic QM perturbation theory assumptions?

How bad is a Computer Science course that doesn't teach Design Patterns?

Writing dialogues for characters whose first language is not English

How can I take a waterfall's effect on a jump into consideration mechanically?

How many proficiencies and languages does a noble half-elf Knowledge Domain cleric start with?

How can I prevent an oracle who can see into the past from knowing everything that has happened?

Querying an array of strings with a string that represents search terms

PostgreSQL array column, querying and grouping by array fieldPostgres: querying jsonb array with an arrayPostgreSQL filtering on array length inside JSONTemporal tables extension error with array columnsHow does postgres store array values?How do I conditionally select elements of an array?Map functions over array with PostgresArray to string sometimes concatenates fewer valueswhy my split string functions are not working with large OBJECT_DEFINITION?Postgres query column with comma-delimited strings for array of strings

1

I have an array of values like:

array ('hello world', 'foo', 'bar') -- terms here have only letters and one space at most

and some text like: 'foo hello-world hello1world'

and I would like to get back from the original array the values foo and hello world filtering out bar.

Is there any way to get that with Postgres?

postgresql array string-splitting string-searching

share|improve this question

edited Sep 25 '17 at 17:00

Evan Carroll

32.5k970221

asked Sep 25 '17 at 16:10

RandomizeRandomize

476215

bumped to the homepage by Community♦ 6 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How do you expect helloXworld to match hello world there is an X in it? How do we know what characters you permit? What about hellosworld?
  
  – Evan Carroll
  Sep 25 '17 at 16:18
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  you are right sorry :) I changed it with a number.
  
  – Randomize
  Sep 25 '17 at 16:24
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That's still really weird. how come you want hello1world to match hello world
  
  – Evan Carroll
  Sep 25 '17 at 16:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If you are interested in more fuzzy matching, Pavel Stěhule has a LIKE version using arrays at postgres.cz/wiki/PostgreSQL_SQL_Tricks#LIKE_to_list_of_patterns
  
  – bma
  Sep 25 '17 at 18:37
  

  
  
  
  

add a comment | 

1

I have an array of values like:

array ('hello world', 'foo', 'bar') -- terms here have only letters and one space at most

and some text like: 'foo hello-world hello1world'

and I would like to get back from the original array the values foo and hello world filtering out bar.

Is there any way to get that with Postgres?

postgresql array string-splitting string-searching

share|improve this question

edited Sep 25 '17 at 17:00

Evan Carroll

32.5k970221

asked Sep 25 '17 at 16:10

RandomizeRandomize

476215

bumped to the homepage by Community♦ 6 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How do you expect helloXworld to match hello world there is an X in it? How do we know what characters you permit? What about hellosworld?
  
  – Evan Carroll
  Sep 25 '17 at 16:18
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  you are right sorry :) I changed it with a number.
  
  – Randomize
  Sep 25 '17 at 16:24
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That's still really weird. how come you want hello1world to match hello world
  
  – Evan Carroll
  Sep 25 '17 at 16:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If you are interested in more fuzzy matching, Pavel Stěhule has a LIKE version using arrays at postgres.cz/wiki/PostgreSQL_SQL_Tricks#LIKE_to_list_of_patterns
  
  – bma
  Sep 25 '17 at 18:37
  

  
  
  
  

add a comment | 

I have an array of values like:

array ('hello world', 'foo', 'bar') -- terms here have only letters and one space at most

and some text like: 'foo hello-world hello1world'

and I would like to get back from the original array the values foo and hello world filtering out bar.

Is there any way to get that with Postgres?

postgresql array string-splitting string-searching

share|improve this question

edited Sep 25 '17 at 17:00

Evan Carroll

32.5k970221

asked Sep 25 '17 at 16:10

RandomizeRandomize

476215

array ('hello world', 'foo', 'bar') -- terms here have only letters and one space at most

share|improve this question

edited Sep 25 '17 at 17:00

Evan Carroll

32.5k970221

asked Sep 25 '17 at 16:10

RandomizeRandomize

476215

share|improve this question

edited Sep 25 '17 at 17:00

Evan Carroll

32.5k970221

asked Sep 25 '17 at 16:10

RandomizeRandomize

476215

edited Sep 25 '17 at 17:00

Evan Carroll

32.5k970221

edited Sep 25 '17 at 17:00

Evan Carroll

32.5k970221

asked Sep 25 '17 at 16:10

RandomizeRandomize

476215

asked Sep 25 '17 at 16:10

RandomizeRandomize

476215

1 Answer
1

active

oldest

votes

0

I would do something like this,

1. create a helper function to generate your query terms from the text-string input. You could inline this into the function, but it's ugly and silly and if you need it here then you probably need it elsewhere.


2. Write a query with NOT word LIKE ANY()
   

Here is a demo of the helper function,

CREATE OR REPLACE FUNCTION my_stupid_query(q text)

RETURNS text[] AS $$



  SELECT ARRAY(

    SELECT regexp_replace(t, '[^[:alpha:]]', '_', 'g')

    FROM unnest(string_to_array(q, ' ')) AS q(t)

  );



$$ LANGUAGE sql

IMMUTABLE;

Then you can use NOT word LIKE ANY(). This shows what it would look like,

SELECT word

FROM (VALUES (ARRAY['hello world', 'foo', 'bar'])) AS t1(x)

CROSS JOIN LATERAL unnest(x) AS t2(word)

WHERE NOT word LIKE ANY(my_stupid_query('foo hello-world helloXworld'));



 word 

------

 bar

(1 row)

share|improve this answer

edited Sep 25 '17 at 17:05

answered Sep 25 '17 at 16:44

Evan CarrollEvan Carroll

32.5k970221

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f186835%2fquerying-an-array-of-strings-with-a-string-that-represents-search-terms%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

I would do something like this,

1. create a helper function to generate your query terms from the text-string input. You could inline this into the function, but it's ugly and silly and if you need it here then you probably need it elsewhere.


2. Write a query with NOT word LIKE ANY()
   

Here is a demo of the helper function,

CREATE OR REPLACE FUNCTION my_stupid_query(q text)

RETURNS text[] AS $$



  SELECT ARRAY(

    SELECT regexp_replace(t, '[^[:alpha:]]', '_', 'g')

    FROM unnest(string_to_array(q, ' ')) AS q(t)

  );



$$ LANGUAGE sql

IMMUTABLE;

Then you can use NOT word LIKE ANY(). This shows what it would look like,

SELECT word

FROM (VALUES (ARRAY['hello world', 'foo', 'bar'])) AS t1(x)

CROSS JOIN LATERAL unnest(x) AS t2(word)

WHERE NOT word LIKE ANY(my_stupid_query('foo hello-world helloXworld'));



 word 

------

 bar

(1 row)

share|improve this answer

edited Sep 25 '17 at 17:05

answered Sep 25 '17 at 16:44

Evan CarrollEvan Carroll

32.5k970221

add a comment | 

0

I would do something like this,

1. create a helper function to generate your query terms from the text-string input. You could inline this into the function, but it's ugly and silly and if you need it here then you probably need it elsewhere.


2. Write a query with NOT word LIKE ANY()
   

Here is a demo of the helper function,

CREATE OR REPLACE FUNCTION my_stupid_query(q text)

RETURNS text[] AS $$



  SELECT ARRAY(

    SELECT regexp_replace(t, '[^[:alpha:]]', '_', 'g')

    FROM unnest(string_to_array(q, ' ')) AS q(t)

  );



$$ LANGUAGE sql

IMMUTABLE;

Then you can use NOT word LIKE ANY(). This shows what it would look like,

SELECT word

FROM (VALUES (ARRAY['hello world', 'foo', 'bar'])) AS t1(x)

CROSS JOIN LATERAL unnest(x) AS t2(word)

WHERE NOT word LIKE ANY(my_stupid_query('foo hello-world helloXworld'));



 word 

------

 bar

(1 row)

share|improve this answer

edited Sep 25 '17 at 17:05

answered Sep 25 '17 at 16:44

Evan CarrollEvan Carroll

32.5k970221

add a comment | 

I would do something like this,

1. create a helper function to generate your query terms from the text-string input. You could inline this into the function, but it's ugly and silly and if you need it here then you probably need it elsewhere.


2. Write a query with NOT word LIKE ANY()
   

Here is a demo of the helper function,

CREATE OR REPLACE FUNCTION my_stupid_query(q text)

RETURNS text[] AS $$



  SELECT ARRAY(

    SELECT regexp_replace(t, '[^[:alpha:]]', '_', 'g')

    FROM unnest(string_to_array(q, ' ')) AS q(t)

  );



$$ LANGUAGE sql

IMMUTABLE;

Then you can use NOT word LIKE ANY(). This shows what it would look like,

SELECT word

FROM (VALUES (ARRAY['hello world', 'foo', 'bar'])) AS t1(x)

CROSS JOIN LATERAL unnest(x) AS t2(word)

WHERE NOT word LIKE ANY(my_stupid_query('foo hello-world helloXworld'));



 word 

------

 bar

(1 row)

share|improve this answer

edited Sep 25 '17 at 17:05

answered Sep 25 '17 at 16:44

Evan CarrollEvan Carroll

32.5k970221

CREATE OR REPLACE FUNCTION my_stupid_query(q text)

RETURNS text[] AS $$



  SELECT ARRAY(

    SELECT regexp_replace(t, '[^[:alpha:]]', '_', 'g')

    FROM unnest(string_to_array(q, ' ')) AS q(t)

  );



$$ LANGUAGE sql

IMMUTABLE;

SELECT word

FROM (VALUES (ARRAY['hello world', 'foo', 'bar'])) AS t1(x)

CROSS JOIN LATERAL unnest(x) AS t2(word)

WHERE NOT word LIKE ANY(my_stupid_query('foo hello-world helloXworld'));



 word 

------

 bar

(1 row)

share|improve this answer

edited Sep 25 '17 at 17:05

answered Sep 25 '17 at 16:44

Evan CarrollEvan Carroll

32.5k970221

answered Sep 25 '17 at 16:44

Evan CarrollEvan Carroll

32.5k970221

answered Sep 25 '17 at 16:44

Evan CarrollEvan Carroll

32.5k970221