Search in all database fields

Question

I want to create search filed for address book in Postgres

AddressBook (pid, tag, address1, address2, city, stateProv, postalCode, ... )

When I type some String into search filed I would like to get all available data from all table columns. What is the proper SQL query for this case?

Something like [here](http://stackoverflow.com/questions/7922744/how-can-i-search-all-columns-in-a-table) should work. You could also consider using FULL-TEXT index — Lukasz Szozda, Apr 03 '16 at 21:37
Possible duplicate of [How to search a specific value in all tables (PostgreSQL)?](http://stackoverflow.com/questions/5350088/how-to-search-a-specific-value-in-all-tables-postgresql) — Tony, Apr 03 '16 at 21:54
Full text index. Is much faster. http://www.postgresql.org/docs/9.2/static/textsearch-tables.html#TEXTSEARCH-TABLES-INDEX I had a table products with 20 k items, and the time of query was not acceptable. Switched to full text index, it seems to be on google. — jurhas, Apr 04 '16 at 09:06

Josh Stevens · Answer 1 · 2016-04-03T21:50:57.437

I guess you be passing in a parameter of the value everytime the user updates the value calling the method each time so you could do something like this:

-- DECLARE @yourSearch varchar(max);
-- This will be passed in your server side code

    SELECT *
    FROM AddressBook
    WHERE pid LIKE '%@yourSearch%' OR 
          tag LIKE '%@yourSearch%' OR  
          address1 LIKE '%@yourSearch%' OR 
          address2 LIKE '%@yourSearch%' OR 
          city LIKE '%@yourSearch%' OR 
          stateProv LIKE '%@yourSearch%' OR
          postalCode LIKE '%@yourSearch%'

This will get all the substring matches if you want to find a exact match you could do something like this:

SELECT *
FROM AddressBook
WHERE @yourSearch IN (pid, tag, address1, address2, city, stateProv, postalCode)

Please note you said ALL so i queried it even against the id of the address which i would of thought you would not want the user to see but i kept it in there just incase - delete every instance of pid if you don't want the id returned in the query and specify your columns names instead of *

Search in all database fields

1 Answers1