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?
--
Posted via http://www.ruby-forum.com/.
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 Giacomoni <rails-mailing-l...@andreas- s.net> wrote:> 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? > -- > Posted viahttp://www.ruby-forum.com/.
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%" ? -- Posted via http://www.ruby-forum.com/.
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 Giacomoni <rails-mailing-l...@andreas- s.net> wrote:> 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%" ? > -- > Posted viahttp://www.ruby-forum.com/.
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 Jones
On Aug 28, 10:30 am, Aldric Giacomoni <rails-mailing-l...@andreas-
s.net> wrote:> 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?
> --
> Posted viahttp://www.ruby-forum.com/.
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 :) -- Posted via http://www.ruby-forum.com/.