Christian Démolis wrote: > Hi, > > The idea of creating another column is good but it will multiplicate > the size of my table by 2 for nothing. > Is it possible to use MYSQL regular expression search with sql alcmehy? > If yes, what is the command? > MySQL supports RLIKE/REGEXP operators; you can see how to use them in SQLAlchemy in this thread: http://groups.google.com/group/sqlalchemy/browse_thread/thread/7c6abe2ab9d5061
Be aware that regexp matching cannot utilize indexes, so you may have performance problems. If you want to get fancy, you can try creating an index on the expression REPLACE(REPLACE(<tel_column>, ' ', ''), '.', '') which should create an index on the normalized phone numbers. In theory, as long as your queries use the exact same function sequence as the index, the index will be scanned instead of the full table. I don't know what limitations MySQL has in this regard. Even if it does work, the only real advantage it provides over using another column is the normalized form is "hidden" in the index instead of the table. Hope it helps, -Conor > 2009/10/10 Andre Stechert <stech...@gmail.com <mailto:stech...@gmail.com>> > > > This is not really a sqlalchemy question, but the quick answer is that > you need to convert both your indexed data and your queries to the > same normal form. In your example, you appear to be correctly > stripping spaces and periods in your query. If you haven't done that > in the database, then you should do it there, too. If you need to > preserve the original formatting of the telephone number column, then > create another column that contains the stripped phone numbers. You > probably also want to put an index on that column. > > Lastly, a minor note on the sample code: you appear to be missing a > "%" operator in your LIKE query. > > Cheers, > Andre > > On Fri, Oct 9, 2009 at 5:46 AM, Christian Démolis > <christiandemo...@gmail.com <mailto:christiandemo...@gmail.com>> > wrote: > > Hi everybody, > > > > I m stuck with a query about telephone number : > > I want to find in my database all the contact who have a > telephone number. > > The difficulty is that some number in the database can have > space or . > > between numbers > > example : 06.06.50.44.11 or 45 87 12 45 65 > > > > This my query with like but it s not what i want because i > ignore telephone > > number who have special chars > > > > NumeroApparu = "0645444444" > > s = session.query(Contact.IdContact, Contact.Civilite, Contact.Nom, > > Contact.Prenom, > > > > ContactTel.Tel).filter(ContactTel.IdContact==Contact.IdContact).filter(Contact.IdDossier==self.dossierPourChargement.IdDossier).filter(ContactTel.Tel.like(NumeroApparu)) > > > > i saw class sqlalchemy.sql.expression.ColumnOperators¶ in the > doc but lack > > of explication... > > > > > > > > > > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
