formulario - Buscando números de teléfono en mysql

busqueda avanzada php mysql

Question

Tengo una tabla que está llena de números de teléfono con formato arbitrario, como este

027 123 5644
021 393-5593
[phone]
042123456

Necesito buscar un número de teléfono en un formato arbitrario similar (por ejemplo, 07123456 debe encontrar la entrada [phone]

La forma en que haría esto en un lenguaje de programación normal es quitar todos los caracteres que no sean dígitos de la ''aguja'', luego revisar cada número en el pajar, quitar todos los caracteres que no sean dígitos y luego comparar la aguja, por ejemplo, (en rubí)

digits_only = lambda{ |n| n.gsub /[^/d]/, '''' }

needle = digits_only[input_phone_number]
haystack.map(&digits_only).include?(needle)

El problema es que necesito hacer esto en MySQL. Tiene una serie de funciones de cadena, ninguna de las cuales parece hacer lo que quiero.

Actualmente puedo pensar en 2 ''soluciones''

• Hackear juntos una consulta de CONCAT de CONCAT y SUBSTR
• Inserte un % entre cada carácter de la aguja (por lo que es así: %0%7%1%2%3%4%5%6% )

Sin embargo, ninguno de estos parece ser una solución particularmente elegante.
Con suerte, alguien puede ayudar o podría verse obligado a utilizar la solución %%%%%%

Actualización: Esto opera sobre un conjunto relativamente fijo de datos, con quizás unos cientos filas. Simplemente no quería hacer algo ridículamente malo que los futuros programadores llorarían.

Si el conjunto de datos crece voy a tomar el enfoque ''phoneStripped''. ¡Gracias por todos los comentarios!

podría usar una función "reemplazar" para eliminar cualquier instancia de "(", "-" y "",

No me preocupa que el resultado sea numérico. Los personajes principales que necesito considerar son + , - , ( , ) y el space Entonces, ¿esa solución se vería así?

SELECT * FROM people 
WHERE 
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phonenumber, ''(''),'')''),''-''),'' ''),''+'')
LIKE ''123456''

¿No sería terriblemente lento?

Answer 1

MySQL puede buscar basado en expresiones regulares.

Claro, pero dado el formato arbitrario, si mi pajar contiene "[phone]" (tenga en cuenta la posición de los espacios puede cambiar, podría ser tan fácilmente 027 12 3456 y quería que coincida con 027123456 , sería mi regex por lo tanto, ¿necesita ser esto?

"^[/D]+0[/D]+2[/D]+7[/D]+1[/D]+2[/D]+3[/D]+4[/D]+5[/D]+6$"

(en realidad sería peor ya que el manual de mysql no parece indicar que es compatible con /D )

Si ese es el caso, ¿no es más o menos lo mismo que mi idea %%%%%?

Answer 2

¿Es posible ejecutar una consulta para reformatear los datos para que coincidan con el formato deseado y luego simplemente ejecutar una consulta simple? De esa manera, incluso si el reformateo inicial es lento, en realidad no importa.

Answer 3

Ay de mí. Terminé haciendo esto:

mre = mobile_number && (''%'' + mobile_number.gsub(//D/, '''').scan(/./m).join(''%''))

find(:first, :conditions => [''trim(mobile_phone) like ?'', mre])

Answer 4

Solo una idea, pero ¿no podrías usar Regex para despojar rápidamente a los personajes y luego compararlos con lo que sugirió @Matt Hamilton?

Tal vez incluso configurar una vista (no estoy seguro de mysql en las vistas) que mantendría todos los números de teléfono eliminados por expresiones regulares a un número de teléfono normal?

Answer 5

Una idea original, pero ¿podría usar una función de "reemplazar" para quitar cualquier instancia de "(", "-" y "", y luego usar una función "isnumeric" para probar si la cadena resultante es un numero?

Luego, podría hacer lo mismo con la cadena de números de teléfono que está buscando y compararlos como números enteros.

Por supuesto, esto no funcionará para números como 1800-MATT-ROCKS. :)

Answer 6

Esto parece un problema desde el principio. Cualquier tipo de búsqueda que haga requerirá una exploración de tabla y todos sabemos que eso es malo.

¿Qué le parece agregar una columna con un hash de los números de teléfono actuales después de eliminar todos los caracteres de formato? Entonces, al menos, puede indexar los valores de hash y evitar un escaneo completo de la tabla.

¿O la cantidad de datos es pequeña y no se espera que crezca demasiado? Entonces tal vez solo chupando todos los números en el cliente y ejecutando una búsqueda allí.

Answer 7

si esto es algo que va a suceder de forma regular, tal vez la modificación de los datos para que sea todo un formato y luego configurar el formulario de búsqueda para eliminar cualquier elemento no alfanumérico (si permite números como 310-BELL) sería una buena idea . Tener datos en un formato de búsqueda fácil es la mitad de la batalla.

Answer 8

Mi solución sería algo similar a lo que dijo John Dyer. Agregaría una segunda columna (por ejemplo, phoneStripped) que se despoja de insertar y actualizar. Indexe esta columna y busque en ella (después de eliminar su término de búsqueda, por supuesto).

También podría agregar un disparador para actualizar automáticamente la columna, aunque no he trabajado con disparadores. Pero como dijiste, es realmente difícil escribir el código MySQL para quitar las cadenas, por lo que probablemente sea más fácil hacerlo en tu código de cliente.

(Sé que es tarde, pero acabo de empezar a buscar por aquí :)

Answer 9

una posible solución se puede encontrar en http: //udf-regexp.php-baustelle.de/trac/

Se necesita instalar un paquete adicional, entonces puedes jugar con REGEXP_REPLACE

Answer 10

sugiero usar funciones php, y no patrones mysql, por lo que tendrá un código como este:

$tmp_phone = '''';
for ($i=0; $i < strlen($phone); $i++)
   if (is_numeric($phone[$i]))
       $tmp_phone .= ''%''.$phone[$i];
$tmp_phone .= ''%'';
$search_condition .= " and phone LIKE ''" . $tmp_phone . "'' ";

Answer 11

Cree una función definida por el usuario para crear dinámicamente Regex.

DELIMITER //

CREATE FUNCTION udfn_GetPhoneRegex
(   
    var_Input VARCHAR(25)
)
RETURNS VARCHAR(200)

BEGIN
    DECLARE iterator INT          DEFAULT 1;
    DECLARE phoneregex VARCHAR(200)          DEFAULT '''';

    DECLARE output   VARCHAR(25) DEFAULT '''';


   WHILE iterator < (LENGTH(var_Input) + 1) DO
      IF SUBSTRING(var_Input, iterator, 1) IN ( ''0'', ''1'', ''2'', ''3'', ''4'', ''5'', ''6'', ''7'', ''8'', ''9'' ) THEN
         SET output = CONCAT(output, SUBSTRING(var_Input, iterator, 1));
      END IF;
      SET iterator = iterator + 1;
   END WHILE;
    SET output = RIGHT(output,10);
    SET iterator = 1;
    WHILE iterator < (LENGTH(output) + 1) DO
         SET phoneregex = CONCAT(phoneregex,''[^0-9]*'',SUBSTRING(output, iterator, 1));
         SET iterator = iterator + 1;
    END WHILE;
    SET phoneregex = CONCAT(phoneregex,''$'');
   RETURN phoneregex;
END//
DELIMITER ;

Llame a esa función definida por el usuario en su procedimiento almacenado.

DECLARE var_PhoneNumberRegex        VARCHAR(200);
SET var_PhoneNumberRegex = udfn_GetPhoneRegex(''+ 123 555 7890'');
SELECT * FROM Customer WHERE phonenumber REGEXP var_PhoneNumberRegex;

Answer 12

Sé que esta es historia antigua, pero la encontré mientras buscaba una solución similar.

Un REGEXP simple puede funcionar:

select * from phone_table where phone1 REGEXP "07[^0-9]*123[^0-9]*456"

Esto coincidiría con la columna phonenumber con o sin caracteres separadores.

Answer 13

Este es un problema con MySQL: la función de expresiones regulares puede coincidir, pero no puede reemplazar. Vea esta publicación para una posible solución.

Answer 14

Usaría libPhoneNumber de Google para formatear un número al formato E164. Agregaría una segunda columna llamada "e164_number" para almacenar el número formateado e164 y agregarle un índice.

Answer 15

Ver

http://www.mfs-erp.org/community/blog/find-phone-number-in-database-format-independent

No es realmente un problema que la expresión regular se vuelva visualmente espantosa, ya que solo mysql "la ve". Tenga en cuenta que en lugar de ''+'' (cfr. Publicar con [/ D] desde el OP) debe usar ''*'' en la expresión regular.

Algunos usuarios están preocupados por el rendimiento (búsqueda no indexada), pero en una tabla con 100000 clientes, esta consulta, cuando se emite desde una interfaz de usuario, vuelve inmediatamente, sin un retraso notable.