# Finding data which, when tweaked, matches

**URL:** <https://www.ruby-forum.com/t/finding-data-which-when-tweaked-matches/174559>\
**Category:** Rails\
**Created:** [August 28, 2009, 4:30pm UTC](https://www.ruby-forum.com/t/finding-data-which-when-tweaked-matches/174559 "2009-08-28T16:30:05Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![trevoke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@trevoke](https://www.ruby-forum.com/u/trevoke)\
**Post date:** [August 28, 2009, 4:30pm UTC](https://www.ruby-forum.com/t/finding-data-which-when-tweaked-matches/174559/1 "2009-08-28T16:30:05Z")

</div>

Say I have a string : “012345678”.  
I have a column in a table which also stores strings. I want to find all  
rows which match that string of numbers in that order.  
That is, “012345678” will match, but “012-34-5678” will, and  
“012sneeze345bless67you8thanks” also will.

Do I have to do something like:  
ThisIsMyString = “012345678”  
fits = Patient.all.find { |a| a.identifier.delete("^[0-9]") ==  
ThisIsMyString }

… Or can I do something WITHOUT getting all the data, a kind of  
Patient.find but with a tweak on the sql query?

---

<div class="post-metadata">

**Author:** ![trevoke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@trevoke](https://www.ruby-forum.com/u/trevoke)\
**Post date:** [August 28, 2009, 5:23pm UTC](https://www.ruby-forum.com/t/finding-data-which-when-tweaked-matches/174559/2 "2009-08-28T17:23:40Z")

</div>

SQL provides a LIKE function for simple pattern matching. Also, both  
mySql and PostgreSql provide REGEX functions though they may not be  
identical in use.

On Aug 28, 10:30 am, Aldric G. \<rails-mailing-l…@andreas-

---

<div class="post-metadata">

**Author:** ![trevoke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@trevoke](https://www.ruby-forum.com/u/trevoke)\
**Post date:** [August 28, 2009, 5:57pm UTC](https://www.ruby-forum.com/t/finding-data-which-when-tweaked-matches/174559/3 "2009-08-28T17:57:47Z")

</div>

Rick Lloyd wrote:

> SQL provides a LIKE function for simple pattern matching. Also, both  
> mySql and PostgreSql provide REGEX functions though they may not be  
> identical in use.

So are you suggesting that I do something like this:

string = params[:social]

# magic on string until string = “%0%1%2%3%4%5%6%7%8%” ?

---

<div class="post-metadata">

**Author:** ![trevoke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@trevoke](https://www.ruby-forum.com/u/trevoke)\
**Post date:** [August 28, 2009, 9:29pm UTC](https://www.ruby-forum.com/t/finding-data-which-when-tweaked-matches/174559/4 "2009-08-28T21:29:31Z")

</div>

Not quite, in PostgreSql you could use

SELECT \* FROM tablename WHERE fieldname SIMILAR TO ‘[-a-z]\*0[-a-z]\*1[-  
a-z]\*2[-a-z]\*3[-a-z]\*4[-a-z]\*5[-a-z]\*6[-a-z]\*7[-a-z]_8[-a-z]_’

to push all the filtering into the query.

On Aug 28, 11:57 am, Aldric G. \<rails-mailing-l…@andreas-

---

<div class="post-metadata">

**Author:** ![trevoke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@trevoke](https://www.ruby-forum.com/u/trevoke)\
**Post date:** [August 29, 2009, 6:27pm UTC](https://www.ruby-forum.com/t/finding-data-which-when-tweaked-matches/174559/5 "2009-08-29T18:27:57Z")

</div>

The SQL regex stuff others have posted is one way to do it, but note  
that scanning a table like that is inherently going to be slow, and  
will get slower as the table gets bigger.

If you’re doing this operation a lot, it might be valuable to pre-  
compute the number strings for the identifier field, like this:

class Patient

# add a new field, identifier\_numbers perhaps

before\_save :sanitize\_indentifier

def sanitize\_identifier  
self.identifier\_numbers = identifier.gsub(/[^0-9]/,"") if  
identifier  
end  
end

Then you can just search against identifier\_numbers.

–Matt J.

On Aug 28, 10:30 am, Aldric G. \<rails-mailing-l…@andreas-

---

<div class="post-metadata">

**Author:** ![trevoke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@trevoke](https://www.ruby-forum.com/u/trevoke)\
**Post date:** [August 31, 2009, 2:53pm UTC](https://www.ruby-forum.com/t/finding-data-which-when-tweaked-matches/174559/6 "2009-08-31T14:53:50Z")

</div>

Matt, your advice is valuable - garbage in, garbage out, right?  
Sadly, I’m working with a whole bunch of legacy data. It won’t grow on  
either side, but I can’t really afford to sanitize the data, either…  
Because I need to know exactly what record I’m looking at 😕

On the other hand, creating a new field with sanitized data and then  
going through all my data could work too. Hmm. Thanks 🙂