I have been trying to figure the following for a couple days. Please HELP
PostgreSQL table : locations
Id State
--------------------
1 New York
2 Texas
input = 'Greetings from Texas to all Cowboys'
output: row containing Texas
SELECT id, state FROM locations WHERE state ~* substring(input from state)
1.
select * from locations where 'Greetings from Texas to all Cowboys' ~ State;
2.
select * from locations where State = any(string_to_array('Greetings from Texas to all Cowboys',' '));
The two methods above both have some problems in some circumstances.But I want to know if they are for you.
3.
select * from locations where 'reetings from Texas to all Cowboys' ~* ('\\m' || state || '\\M');
The last method would be more better.
A search word is not a pattern. Try this:
select * from locations where 'Hello from Texas!' like '%' || state || '%';
or this:
select * from locations where 'Hello from Texas!' ~* ('.*' || state || '.*');
if you want Posix regexp's.
Example:
# create table locations(id integer, state text);
CREATE TABLE
# insert into locations values (1,'New York'),(2,'Texas') ;
INSERT 0 2
# select * from locations where 'Hello from Texas!' like '%' || state || '%';
id | state
----+-------
2 | Texas
(1 row)
# select * from locations where 'Hello from Texas!' ~* ('.*' || state || '.*');
id | state
----+-------
2 | Texas
(1 row)
# select * from locations where 'Greetings from you ex' like '%' || state || '%';
id | state
----+-------
(0 rows)
# select * from locations where 'Greetings from your ex' ~* ('.*' || state || '.*');
id | state
----+-------
(0 rows)
This needs some refinement or course, if you need to detect word boundaries:
# select * from locations where 'fakulos greekos metexas' ~* ('.*' || state || '.*');
id | state
----+-------
2 | Texas
If you have regex-metacharacters (See the PostgresSQL docs for as list) in your search words, then you might need to quote them first. This look a bit weird but this is what escaping always looks like:
select regexp_replace('Dont mess (with) Texas, The Lone *',E'([\(\)\*])',E'\\\\\\1','g');
The ([\(\)\*]) is the list of characters you want to escape.
However, if you never need regular expressions in your search words, then it might be easier to use a simple string searching function like strpos():
select strpos('Dont mess (with) Texas','Texas')>0;
?column?
--------
t
select strpos('Dont mess (with) Texas','Mars')>0;
?column?
--------
f
You can use upper() if you want case insensitive compares
select strpos(upper('Dont mess (with) Texas'),upper('teXas'))>0;
?column?
--------
t
I would take a look at full text search:
SELECT
id,
state
FROM
locations
WHERE
to_tsvector('english', 'Greetings from Texas to all Cowboys') @@ plainto_tsquery('english', state);
Standard available as of version 8.3, in older versions you have to install tsearch2 from the contrib.
http://www.postgresql.org/docs/current/interactive/textsearch.html
