I've been attempting to figure the next a couple of days. Help

PostgreSQL table : locations

Id         State

--------------------
1          New York

2          Texas

input = 'Greetings from Texas to any or all Cowboys'

output: row that contains Texas

SELECT id, state FROM locations WHERE state ~* substring(input from state)

Searching word isn't a pattern. Do this:

select * from locations where 'Hello from Texas!' like '%' || state || '%';

or this:

select * from locations where 'Hello from Texas!' ~* ('.*' || state || '.*');

if you would like 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 want to identify word limitations:

# select * from locations where 'fakulos greekos metexas' ~* ('.*' || state || '.*');
id | state
----+-------
2 | Texas

For those who have regex-metacharacters (Begin to see the PostgresSQL paperwork as list) during your search words, then you will need to quote them first. This look a little strange but this is exactly what getting away always appears like:

select regexp_replace('Dont mess (with) Texas, The Lone *',E'([\(\)\*])',E'\\\\\\1','g');

The ([\(\)\*]) may be the listing of figures you need to escape.

However, should you never need frequent expressions during your search words, then it may be simpler to utilize 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 should use upper() if you would like situation insensitive compares

select strpos(upper('Dont mess (with) Texas'),upper('teXas'))>0;
?column?
--------
t

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 2 techniques above have some problems in certain conditions.But I wish to know if they're for you personally.

3.

select * from locations where 'reetings from Texas to all Cowboys' ~* ('\\m' || state || '\\M');

The final method could be more better.

I'd have 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 by version 8.3, in older versions you need to install tsearch2 in the contrib.

http://www.postgresql.org/docs/current/interactive/textsearch.html