Search in every column

Question

I'm building an abstract gem. i need a sql query that looks like this

SELECT * FROM my_table WHERE * LIKE '%my_search%'

is that possible?

edit: I don't care about querys performance because it's a feature function of a admin panel, which is used once a month. I also don't know what columns the table has because it's so abstract. Sure i could use some rails ActiveRecord functions to find all the columns but i hoped to avoid adding this logic and just using the *. It's going to be a gem, and i can't know what db is going to be used with it. Maybe there is a sexy rails function that helps me out here.

[Why do you want to do this](http://www.catb.org/~esr/faqs/smart-questions.html#goal)? — outis, Feb 11 '11 at 09:25
Just be aware - if you use an expression such as `LIKE '%term%'` you basically prevent the query optimizer from using any kind of index. You'll get full table scans every time. Not very good in terms of performance. To search for a word in several columns, use **Fulltext Searching** — marc_s, Feb 11 '11 at 10:02
"I don't care about querys performance" + "prevent the query optimizer from using any kind of index." => can crash the system, or at least use all its ressources for too long and prevent any other queries that may be critical to the business. (A bad dirty query made once my company's server crash, had to be rebooted, forbidding workers to do their job for 15 minutes each time). Care about performance :) — Cedric, Feb 11 '11 at 11:00
Does my solution work for you? I am curious! Please let us know! — Syed Aslam, Feb 11 '11 at 13:43
http://pastebin.com/raw.php?i=7nuF5gLY yes thanks im using this code now, its good enough for a default search in a abstract admin panel — antpaw, Feb 11 '11 at 18:00

score 2 · Accepted Answer · answered Feb 11 '11 at 11:26

2

As I understand the question, basically you are trying to build a sql statement which should check for a condition across all columns in that table. A dirty hack, but this generates the required Sql.

condition_string = MyTable.column_names.join(' LIKE ? OR ')
MyTable.all(:conditions => [condition_string, '%my_search%'])

However, this is not tested. This might work.

answered Feb 11 '11 at 11:26

Syed Aslam

8,707
5
40
54

Actually this fails. It needs the search term duplicated MyTable.column_names.size times! – Syed Aslam Feb 11 '11 at 11:33
hi @antpaw, @Sam's method should work more railly :D .. give it a try and let us know – sameera207 Feb 11 '11 at 12:10

score 1 · Answer 2 · edited May 23 '17 at 12:20

1

* LIKE '...' isn't valid according to the SQL standards, and not supported by any RDBMS I'm aware of. You could try using a function like CONCAT to make the left argument of LIKE, though performance won't be good. As for SELECT *, it's generally something to be avoided.

edited May 23 '17 at 12:20

Community

1
1

answered Feb 11 '11 at 09:23

outis

75,655
22
151
221

1

I don't think it is his main concern here, he wants to know if it is allowed to do something like `where *` in SQL (i.e. `where col1 = somecondition or col2 = somecondition or col3 = ...` – Romain Linsolas Feb 11 '11 at 09:25

score 0 · Answer 3 · edited May 23 '17 at 12:29

No, SQL does not support that syntax.

To search all columns you need to use procedures or dynamic SQL. Here's another SO question which may help:

SQL: search for a string in every varchar column in a database

EDIT: Sorry, the question I linked to is looking for a field name, not the data, but it might help you write some dynamically SQL to build the query you need.

You didn't say which database you are using, as there might be a vendor specific solution.

CloudyMarble · Answer 4 · 2011-02-11T11:48:12.607

Its only an Idea, but i think it worth testing!

It depends on your DB you can get all Columns of a table, in MSSQL for example you can use somethink like:

select name from syscolumns where id=object_id('Tablename')

Under Oracle guess its like:

select column_name from USER_TAB_COLUMNS where TABLE_NAME = 'Tablename'

and then you will have to go through these columns usign a procedure and maby a cursor so you can check for each Column if the data your searching for is in there:

if ((select count(*) from Tablename where Colname = 'searchingdata') > 0) 
then keep the results in a separated table(ColnameWhereFound, RecNrWhereFound).

The matter of Datatye may be an Issue if you try to compare strings with numbers, but if you notice for instance under SQL-Server the syscolumns table contains a column called "usertype" which contains a number seems to refer to the Datatype stored in the Columne, like 2 means string and 7 means int, and 2 means smallint, guess Oracle would have something similar too.

Hope this helps.

Search in every column

4 Answers4