Results 1 to 5 of 5

Thread: General search

  1. #1
    Join Date
    Sep 2010
    Posts
    40

    Unanswered: General search

    Hi,
    I would like to search a whole table (all clomns and rows) with a cretain search term. Is there any way for this more efficient than looping over all columns?
    Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    If you don't want to use any external tools, than "manually" looping over all columns will be your only choice I guess.

    If this is for a specific table where you know the columns, you might want to try concatenating all columns and then search on that string:

    Code:
    SELECT *
    FROM the_table
    WHERE cast(column_1 as text)||to_char(date_column, 'yyyy-mm-dd')||cast(column_3 as text) LIKE '%some_value%';
    But this will get ugly when you have more columns. Also you should probably use some delimiter between the columns.

  3. #3
    Join Date
    Sep 2010
    Posts
    40
    Thanks,
    which external tools did you have in mind?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    The only one I know that can do this is SQL Workbench/J

    It has a special (i.e. proprietary) "SQL" command to search through all columns in all tables (or a list of tables):
    http://www.sql-workbench.net/manual/wb-commands.html#command-search-data

    And it can be done through the GUI as well, this looks like this then:
    http://www.sql-workbench.net/SearchTables_png.html

  5. #5
    Join Date
    Nov 2006
    Posts
    82
    if you use linux just select all data from a table to the system output and grep for a string

    Code:
    psql -c "select * from ..." databasename  | grep "search for a string" > "write_result_to_a_file"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •