Recherche de numéros de téléphone dans mysql
J'ai une table qui est plein d'arbitraire, numéros de téléphone au format, à l'instar de ce
027 123 5644
021 393-5593
(07) 123 456
042123456
J'ai besoin de rechercher un numéro de téléphone à un de la même façon arbitraire format ( par exemple, 07123456
doit trouver l'entrée (07) 123 456
La façon dont je le ferais dans un langage de programmation est de supprimer tous les chiffres de la 'aiguille', puis allez à travers chaque nombre dans la botte de foin, bande de tous les non-chiffres hors de lui, puis de le comparer à l'encontre de l'aiguille, par exemple, (en ruby)
digits_only = lambda{ |n| n.gsub /[^\d]/, '' }
needle = digits_only[input_phone_number]
haystack.map(&digits_only).include?(needle)
Le hic, c'est que j'ai besoin de le faire dans MySQL. Il a une foule de fonctions de chaîne, aucune ne semble vraiment à faire ce que je veux.
Actuellement je ne peux penser à de 2 "solutions"
- Hack ensemble un franken-requête de
CONCAT
etSUBSTR
- Insérer un
%
entre chaque caractère de l'aiguille ( c'est comme cela:%0%7%1%2%3%4%5%6%
)
Cependant, aucune de ces semblent comme particulièrement élégant solutions.
J'espère que quelqu'un peut aider ou j'ai peut-être forcé à utiliser le %%%%%% solution
Mise à jour: C'est de l'exploitation sur une période relativement fixe de données, avec peut-être quelques centaines de lignes. Je n'avais juste pas envie de faire quelque chose de ridiculement mauvais qu'à l'avenir les programmeurs pleurer.
Si le jeu de données grandit, je vais prendre le 'phoneStripped' approche. Merci pour tous les commentaires!
pourriez-vous utiliser une fonction "remplacer" de supprimer toutes les instances de "(", "-" et " ",
Je ne suis pas préoccupé par le résultat numérique.
Les personnages principaux, j'ai besoin à considérer sont +
, -
, (
, )
et space
Alors qu'solution ressembler à ceci?
SELECT * FROM people
WHERE
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phonenumber, '('),')'),'-'),' '),'+')
LIKE '123456'
N'est-ce pas terriblement lent?
source d'informationauteur Orion Edwards
Vous devez vous connecter pour publier un commentaire.
Cela ressemble à un problème dès le début. Tout le type de recherche que vous ne nécessiteront une analyse de la table et nous savons tous que c'est mal.
Comment sur l'ajout d'une colonne avec une valeur de hachage de l'actuel numéros de téléphone après suppression de tous les caractères de mise en forme. Ensuite, vous pouvez au moins l'indice des valeurs de hachage et d'éviter une véritable analyse de la table.
Ou est la quantité de données de petite et ne devrait pas croître beaucoup?
Alors peut-être juste sucer tous les numéros dans le client et l'exécution d'une recherche.
Je sais que c'est de l'histoire ancienne, mais je l'ai trouvé en cherchant une solution similaire.
Un simple REGEXP peut travailler:
Cela pourrait correspondre à la
phonenumber
colonne avec ou sans séparation des caractères.Dehors-de-le-boîte à idée, mais pourriez-vous utiliser une fonction "remplacer" de supprimer toutes les instances de "(", "-" et " ", et ensuite utiliser un "isnumeric" fonction pour tester si la chaîne résultante est le nombre?
Alors vous pourriez faire la même chose pour la chaîne du numéro de téléphone que vous recherchez et comparez-les comme des nombres entiers.
Bien sûr, cela ne fonctionne pas pour les numéros de 1800-MATT-ROCHES. 🙂
Ma solution serait quelque chose le long des lignes de ce que John Dyer a dit. J'aimerais ajouter une deuxième colonne (par exemple, phoneStripped) qui est dépouillé sur insérer et mettre à jour. L'indice de cette colonne et de la recherche (après décapage de votre terme de recherche, bien sûr).
Vous pouvez également ajouter un déclencheur pour mettre automatiquement à jour la colonne, bien que je n'ai pas travaillé avec des déclencheurs. Mais comme vous l'avez dit, il est vraiment difficile d'écrire le code de MySQL pour dépouiller les cordes, donc c'est probablement plus facile de le faire dans votre code client.
(Je sais c'est tard, mais je viens de commencer à regarder autour de ici 🙂
je suggère d'utiliser les fonctions de php, et pas mysql modèles, de sorte que vous aurez un peu de code comme ceci:
C'est un problème avec MySQL, la fonction regex peut correspondre, mais il ne peut pas la remplacer. Voir ce post pour une solution possible.
Est-il possible d'exécuter une requête pour reformater les données correspondant à un format de votre choix, et puis il suffit d'exécuter une requête simple? De cette façon, même si, au départ, le reformatage est lent, vous, il n'a pas vraiment d'importance.
Voir
http://www.mfs-erp.org/community/blog/find-phone-number-in-database-format-independent
Il n'est pas vraiment un problème que l'expression régulière qui allait devenir visuellement épouvantable, depuis que mysql "voit". Notez qu'au lieu de '+' (cfr. post [\D] de l'OP), vous devez utiliser '*' dans l'expression régulière.
Certains utilisateurs sont préoccupés par la performance (non indexé de recherche), mais dans une table avec 100000 clients, cette requête, lorsqu'il est émis à partir d'une interface utilisateur retourne immédiatement, sans retard notable.
Sûr, mais compte tenu de l'arbitraire de la mise en forme, si ma botte de foin contenues
"(027) 123 456"
(garder à l'esprit la position de l'espace peut changer, il pourrait tout aussi facilement être027 12 3456
et je voulais le faire correspondre avec027123456
mon regex donc besoin de ceci?(en fait, il serait pire que le manuel mysql ne semble pas indiquer qu'il prend en charge
\D
)Si c'est le cas, n'est-il pas plus ou moins la même chose que mes %%%%% idée?
Juste une idée, mais ne pourriez-vous pas utiliser les Regex pour rapidement éliminer les caractères, puis comparez-les avec que comme @Matt Hamilton a suggéré?
Peut-être même mis en place un point de vue (pas sûr de mysql sur les points de vue) qui contiendrait tous les numéros de téléphone dépouillé par regex pour un simple numéro de téléphone?
Malheur, c'est moi. J'ai fini par faire ceci:
si c'est quelque chose qui va se produire sur une base régulière peut-être modifier les données à tous un format, puis le programme d'installation le formulaire de recherche pour traquer des non-alphanumérique (si vous permettez à des chiffres comme au 310-BELL) serait une bonne idée. D'avoir des données dans une facilement recherché format est la moitié de la bataille.
une solution possible peut être trouvé à l'adresse http: //udf-regexp.php-baustelle.de/trac/
forfaits supplémentaires doivent être installés, alors vous pouvez jouer avec REGEXP_REPLACE
Créer une fonction définie par l'utilisateur pour crée dynamiquement des Regex.
Appel de Fonction Définie par l'Utilisateur dans la procédure stockée.
Je voudrais utiliser Google libPhoneNumber pour formater un nombre de format E164. Je voudrais ajouter un deuxième colonne appelée "e164_number" pour stocker les e164 nombre formaté, et d'ajouter un index sur elle.