Finding data which, when tweaked, matches

Rails
1

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?

2

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-

3

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%” ?

4

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-

5

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-

6

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 :confused:

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