# How to search all fields for a string in SQL?

**URL:** <https://www.ruby-forum.com/t/how-to-search-all-fields-for-a-string-in-sql/166924>\
**Category:** Rails\
**Created:** [April 30, 2009, 10:46pm UTC](https://www.ruby-forum.com/t/how-to-search-all-fields-for-a-string-in-sql/166924 "2009-04-30T22:46:16Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [April 30, 2009, 10:46pm UTC](https://www.ruby-forum.com/t/how-to-search-all-fields-for-a-string-in-sql/166924/1 "2009-04-30T22:46:16Z")

</div>

Using SQL or activerecord I am not sure how to search all fields for a  
specific value ?  
I know how to do this by just writing ruby code. To do it in SQL or an  
activerecord find w/condition,  
I don’t know how. I actually am supposed to combine an find w/  
condition and a search all fields for a  
value and AND those together. I think perhaps it can’t be done with a  
single find ?

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [April 30, 2009, 10:54pm UTC](https://www.ruby-forum.com/t/how-to-search-all-fields-for-a-string-in-sql/166924/2 "2009-04-30T22:54:28Z")

</div>

removed\_email\_address@domain.invalid wrote:

> Using SQL or activerecord I am not sure how to search all fields for a  
> specific value ?  
> I know how to do this by just writing ruby code. To do it in SQL or an  
> activerecord find w/condition,  
> I don’t know how. I actually am supposed to combine an find w/  
> condition and a search all fields for a  
> value and AND those together. I think perhaps it can’t be done with a  
> single find ?

What you’re probably looking for is a full-text search engine. Here’s an  
article I found using the Sphinx engine:

[http://www.neeraj.name/blog/articles/599-working-with-sphinx-fulltext-search-engine-using-ultrasphinx](http://www.neeraj.name/blog/articles/599-working-with-sphinx-fulltext-search-engine-using-ultrasphinx)

If you needs are pretty basic and you don’t mind a database specific  
implementation you can also look at MySQL’s full-text search  
capabilities. SQL was just not designed for such queries.

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [April 30, 2009, 11:06pm UTC](https://www.ruby-forum.com/t/how-to-search-all-fields-for-a-string-in-sql/166924/3 "2009-04-30T23:06:46Z")

</div>

I would suggest going with the thinkingsphinx plugin instead of  
ultrasphinx. Thinkingsphinx’s syntax is much cleaner.

[http://ts.freelancing-gods.com/usage.html](http://ts.freelancing-gods.com/usage.html)

> **[\#120 Thinking Sphinx - RailsCasts](http://railscasts.com/episodes/120-thinking-sphinx)**
>
> Short Ruby on Rails screencasts containing tips, tricks and tutorials. Great for both novice and experienced web developers.

On the other hand, if your dataset is small enough, you could try  
scoped-search, which helps you construct the SQL to perform your  
searches: [http://wiki.github.com/wvanbergen/scoped\_search](http://wiki.github.com/wvanbergen/scoped_search)  
But as Robert said, databases/SQL are not great for this type of  
queries and you may not be able to scale much when you have many  
conditions and/or big volumes of data.

On Apr 30, 4:54 pm, Robert W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)