php - List matching records in PostregSQL -

i working on search section comparing input values stored records. database having value ab 09 c d 1234. have explode (with space) input string match stored value when given input ab09 cd 1234, not result above record.

select * some_vehicle vehicle_number ilike e\'%ab09 cd 1234%'  

vehicle_number in db: ab 09 c d 1234
input string: ab09 cd 1234

i want result having every possible scenario combination of input string. there way in postgresql?

"replace" function able eliminate blank space string. there way in postgresql eliminate special characters along blank space. trying different regular expressions

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

thanks

figured out after few attempts! close it. "[^a-za-z0-9]" pattern eliminate special characters including space 'g' option fourth parameter of regexp_replace in postgresql.

you this, eliminating spaces both values:

select * some_vehicle replace(lower(vehicle_number), ' ', '') =       replace(lower('ab09 cd 1234'), ' ', ''); 

or, if want retain substring matching:

select * some_vehicle replace(lower(vehicle_number), ' ', '')       ('%' || replace(lower('ab09 cd 1234'), ' ', '') || '%'); 

note unable use whatever index have on vehicle_number , will result in seq scan (or possibly index scan, if lucky), unless create specialized expression index handle query efficiently:

create index on some_vehicle (( replace(lower(vehicle_number), ' ', '') )); 

(the substring query using like unable use index, since pattern starts %.)

another alternative, if vehicle_number values have spaces in same place store these values without spaces, , insert spaces in display layer (or in view) since know going be. can strip spaces out of search value.