1

I'm fairly new to Spring Boot and I need to create a get request which has some parameters (a part of a phone number) and based on that parameter it should search a postgres table and find the best matches, all while ignoring possible dashes or spaces in that phone number column. How should this be done? Any advice is welcome...

Zijah
  • 11
  • 1
  • Does this answer your question? [How do I remove all spaces from a field in a Postgres database in an update query?](https://stackoverflow.com/questions/20376579/how-do-i-remove-all-spaces-from-a-field-in-a-postgres-database-in-an-update-quer) – Simon Martinelli Apr 03 '21 at 12:52
  • I think I should keep the data in the database as is, but filter it in a query or something like that. – Zijah Apr 03 '21 at 12:55
  • Yes you can use replace in the where condition as well. This will not change the data – Simon Martinelli Apr 03 '21 at 13:09

1 Answers1

1

you should use function base index to speed the query:

CREATE INDEX test1_phone_nospace_idx ON test1 (translate(phonenum, ' -', ''));

you can query your data with

select * from test1 where translate(phonenum, ' -', '') = '+155512123443';

mshabou
  • 524
  • 3
  • 6