CODEWITHSUNDEEP
phpsqlpostgresqlindexingpattern-matching
Technical Amit
Technical Amit

Reputation: 23

List matching records in PostregSQL

I am working on search section where comparing input values with the stored records. Database having value like AB 09 C D 1234. I have explode (with space) input string to match with stored value but when I given input like AB09 CD 1234, it does not result above record. 

SELECT * FROM some_vehicle WHERE vehicle_number ILIKE E\'%AB09 CD 1234%'

vehicle_number in db: AB 09 C D 1234
input string: AB09 CD 1234

I want to result having every possible scenario with combination of input string. Is there any way to do that in PostgreSQL?

"replace" function able to eliminate blank space from the string. Is there any way in PostgreSQL to eliminate all special characters too along with blank space. I was trying different regular expressions like

regexp_replace(vehicle_number,'[^a-z0-9\s]', '') and regexp_replace(vehicle_number,'[(|-|)|\:|\@|#|\$|*|.|!|\,]', '') but its not working.

Thanks

Figured it out after few attempts! was very close to it. "[^a-zA-Z0-9]" pattern eliminate all special characters including space with 'g' option as the fourth parameter of regexp_replace in PostgreSQL.

Upvotes: 0

Views: 121

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656706

If you want to use an index with a query like the one provided by @cdhowie (which you should, if this is a common query on a big table), use a functional trigram index provided by the additional module pg_trgm:

CREATE EXTENSION pg_trgm; -- once per database

CREATE INDEX some_name_idx ON some_vehicle
USING GIN (replace(vehicle_number, ' ', '')  gin_trgm_ops);

I didn't use lower() since that's not an issue in your question. The matching query would be:

SELECT * FROM some_vehicle
WHERE replace(vehicle_number, ' ', '')
      LIKE ('%' || replace('AB09 CD 1234', ' ', '') ||  '%');

Related answer on SO:
Effectively query on column that includes a substring
Or this one on dba.SE.

Upvotes: 1

cdhowie
cdhowie

Reputation: 169008

You could do something like this, eliminating all spaces from both values:

SELECT * FROM some_vehicle
WHERE replace(lower(vehicle_number), ' ', '') =
      replace(lower('AB09 CD 1234'), ' ', '');

Or, if you want to retain substring matching:

SELECT * FROM some_vehicle
WHERE replace(lower(vehicle_number), ' ', '') LIKE
      ('%' || replace(lower('AB09 CD 1234'), ' ', '') || '%');

Note that this will be unable to use whatever index you currently have on vehicle_number and will result in a seq scan (or possibly an index scan, if you are lucky), unless you create a specialized expression index to handle this query efficiently:

CREATE INDEX ON some_vehicle (( replace(lower(vehicle_number), ' ', '') ));

(The substring query using LIKE will be unable to use any index, since the pattern starts with %.)

Another alternative, if your vehicle_number values always have spaces in exactly the same place is to store these values without spaces, and insert the spaces in the display layer (or in a view) since you know exactly where they are going to be. Then you can simply strip spaces out of any search value.

Upvotes: 2

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125234

If you mean combination of space then:

SELECT * FROM some_vehicle WHERE vehicle_number ILIKE E\'%A%B%0%9%C%D%1%2%3%4%'

Upvotes: 0

Related Questions