pratical example of Postgres regex substring or regexp_matches

Question

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)

Answer 1

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

Answer 2

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.

Answer 3

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