strip_tags - replace mysql

Eliminar todos los caracteres no numéricos de un campo

Question

Tengo un conjunto de resultados de un formulario web que incluye un número de teléfono para cada conjunto. el formato de este número de teléfono no se aplica (algunos son xxxxxxxxxx, algunos son (xxx) xxx-xxxx y algunos son xxx-xxx-xxxx). Fue miope, y ahora necesito poder obtener un resultado basado en el número de teléfono (filtro de vistas expuestas).

La mejor manera de resolver esto es reformatear los valores en este campo con una consulta sql para que se eliminen los valores no numéricos. He intentado un par de funciones que he encontrado en preguntas similares, y ninguna parece estar funcionando (estoy usando mysql workbench y obtengo un error de "función no existe"). Esto es algo que hago una vez y estoy buscando una consulta que pueda ejecutar que elimine todos los valores no numéricos. Solo tendré que ejecutarlo una vez, porque a partir de ahora estoy validando los números de teléfono para que sean numéricos.

¿Hay una consulta SQL que haga lo que necesito? Con PHP, simplemente sería

update table set data = preg_replace("/[^0-9]/", "", data) where condition

Pero parece que no puedo encontrar la forma de hacerlo con SQL.

Answer 1

Esto puede funcionar pero poco tedioso:

Obtener la lista de números de la tabla

$result = mysql_query("Select ID, number from Table"); 

En cada valor

while ($row = mysql_fetch_array($result)) {
$ID = $row["ID"];
$NUM = $row["number"]; 

Luego, haz un patrón de expresiones regulares y actualiza ese valor a la ID

$NUM = eregi_replace("whateverregex","",$NUM);
$sql = mysql_query("Update Table set number = $NUM where ID = $ID");
} 

Answer 2

No hay ninguna función "incorporada" que realice esta operación en MySQL.

Una opción es crear su propia función almacenada (si tiene suficientes privilegios en la base de datos).

 DELIMITER $$
DROP FUNCTION IF EXISTS `uf_only_digits`$$
CREATE FUNCTION `uf_only_digits`(as_val VARCHAR(65535))
RETURNS VARCHAR(65535)
DETERMINISTIC
BEGIN
DECLARE retval VARCHAR(65535);
DECLARE i INT;
DECLARE strlen INT;
-- shortcut exit for special cases
IF as_val IS NULL OR as_val = '''' THEN
RETURN as_val;
END IF;
-- initialize for loop
SET retval = '''';
SET i = 1;
SET strlen = CHAR_LENGTH(as_val);
do_loop:
LOOP
IF i > strlen THEN
LEAVE do_loop;
END IF;
IF SUBSTR(as_val,i,1) IN (''0'',''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'',''9'') THEN
SET retval = CONCAT(retval,SUBSTR(as_val,i,1));
END IF;
SET i = i + 1;
END LOOP do_loop;
RETURN retval;
END$$
DELIMITER ;

Y asegúrese de probar esto antes de usarlo como una instrucción UPDATE.

 SELECT t.foo
, uf_only_digits(t.foo)
FROM ( SELECT '''' AS foo
UNION ALL SELECT '' x''
UNION ALL SELECT ''a1b2''
UNION ALL SELECT ''1-888-555-1212 ext 213''
UNION ALL SELECT ''1-800-FLOWERS''
) t

Devoluciones:

 foo uf_only_digits(t.foo) newlen
---------------------- --------------------- --------
0
x 0
a1b2 12 2
1-888-555-1212 ext 213 18885551212213 14
1-800-FLOWERS 1800 4

(Las últimas dos filas podrían darnos una pausa para reconsiderar lo que realmente queremos lograr. Si fuera yo, crearía una nueva columna y guardaría el valor existente en ella, antes de hacer una ACTUALIZACIÓN).

 -- new column same size as `phone` column
ALTER TABLE mytable ADD COLUMN orig_phone VARCHAR(40) NULL
COMMENT ''original phone value, before update to all digits'';
UPDATE mytable t
SET t.orig_phone = t.phone ;
UPDATE mytable t
SET t.phone = uf_only_digits(t.phone) ;

Answer 3

No es bonito, pero ...

UPDATE table
SET data =
CONCAT(
IF (SUBSTRING(data, 01, 1) REGEXP ''[0-9]'', SUBSTRING(data, 01, 1), '''')
, IF (SUBSTRING(data, 02, 1) REGEXP ''[0-9]'', SUBSTRING(data, 02, 1), '''')
, IF (SUBSTRING(data, 03, 1) REGEXP ''[0-9]'', SUBSTRING(data, 03, 1), '''')
, IF (SUBSTRING(data, 04, 1) REGEXP ''[0-9]'', SUBSTRING(data, 04, 1), '''')
, IF (SUBSTRING(data, 05, 1) REGEXP ''[0-9]'', SUBSTRING(data, 05, 1), '''')
, IF (SUBSTRING(data, 06, 1) REGEXP ''[0-9]'', SUBSTRING(data, 06, 1), '''')
, IF (SUBSTRING(data, 07, 1) REGEXP ''[0-9]'', SUBSTRING(data, 07, 1), '''')
, IF (SUBSTRING(data, 08, 1) REGEXP ''[0-9]'', SUBSTRING(data, 08, 1), '''')
, IF (SUBSTRING(data, 09, 1) REGEXP ''[0-9]'', SUBSTRING(data, 09, 1), '''')
, IF (SUBSTRING(data, 10, 1) REGEXP ''[0-9]'', SUBSTRING(data, 10, 1), '''')
, IF (SUBSTRING(data, 11, 1) REGEXP ''[0-9]'', SUBSTRING(data, 11, 1), '''')
, IF (SUBSTRING(data, 12, 1) REGEXP ''[0-9]'', SUBSTRING(data, 12, 1), '''')
, IF (SUBSTRING(data, 13, 1) REGEXP ''[0-9]'', SUBSTRING(data, 13, 1), '''')
, IF (SUBSTRING(data, 14, 1) REGEXP ''[0-9]'', SUBSTRING(data, 14, 1), '''')
, IF (SUBSTRING(data, 15, 1) REGEXP ''[0-9]'', SUBSTRING(data, 15, 1), '''')
, IF (SUBSTRING(data, 16, 1) REGEXP ''[0-9]'', SUBSTRING(data, 16, 1), '''')
)
WHERE condition

Es posible que tenga que duplicar la función IF concatenada más veces según la longitud del campo y la cantidad de formato que se ingresó.

Ver la consulta en acción en SQLFiddle