PostgreSQL String search for partial patterns removing exrtaneous characters

Question

Looking for a simple SQL (PostgreSQL) regular expression or similar solution (maybe soundex) that will allow a flexible search. So that dashes, spaces and such are omitted during the search. As part of the search and only the raw characters are searched in the table.:

Currently using:

SELECT * FROM Productions WHERE part_no ~* '%search_term%'

If user types UTR-1 it fails to bring up UTR1 or UTR 1 stored in the database.

But the matches do not happen when a part_no has a dash and the user omits this character (or vice versa)

EXAMPLE search for part UTR-1 should find all matches below.

UTR1 
UTR --1 
UTR  1

any suggestions...

davek · Accepted Answer · 2010-03-24 19:38:53Z

You may well find the offical, built-in (from 8.3 at least) fulltext search capabilities in postrgesql worth looking at:

http://www.postgresql.org/docs/8.3/static/textsearch.html

For example:

It is possible for the parser to produce overlapping tokens from the 
same of text. 

As an example, a hyphenated word will be reported both as the entire word 
and as each component: 

SELECT alias, description, token FROM ts_debug('foo-bar-beta1');

      alias      |               description                |     token     
-----------------+------------------------------------------+---------------
 numhword        | Hyphenated word, letters and digits      | foo-bar-beta1
 hword_asciipart | Hyphenated word part, all ASCII          | foo
 blank           | Space symbols                            | -
 hword_asciipart | Hyphenated word part, all ASCII          | bar
 blank           | Space symbols                            | -
 hword_numpart   | Hyphenated word part, letters and digits | beta1

davek · Accepted Answer · 2011-01-14 14:45:31Z

2

SELECT  *
FROM    Productions
WHERE   REGEXP_REPLACE(part_no, '[^[:alnum:]]', '') 
      = REGEXP_REPLACE('UTR-1', '[^[:alnum:]]', '')

Create an index on REGEXP_REPLACE(part_no, '[^[:alnum:]]', '') for this to work fast.

edited Jan 14, 2011 at 14:45

davek

23k11 gold badges80 silver badges97 bronze badges

answered Mar 24, 2010 at 19:27

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Collectives™ on Stack Overflow

PostgreSQL String search for partial patterns removing exrtaneous characters

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related