45 lines
1.1 KiB
SQL
45 lines
1.1 KiB
SQL
|
|
USE vn2008;
|
|
|
|
-- Updates the bad formated phones
|
|
|
|
UPDATE Clientes
|
|
SET telefono = pbx.phone_format (telefono),
|
|
fax = pbx.phone_format (fax),
|
|
movil = pbx.phone_format (movil);
|
|
|
|
UPDATE Consignatarios
|
|
SET telefono = pbx.phone_format (telefono),
|
|
movil = pbx.phone_format (movil);
|
|
|
|
UPDATE Contactos
|
|
SET telefono = pbx.phone_format (telefono),
|
|
fax = pbx.phone_format (fax),
|
|
movil = pbx.phone_format (movil);
|
|
|
|
-- Indexes the phone columns
|
|
|
|
ALTER TABLE Clientes ADD INDEX(Telefono);
|
|
ALTER TABLE Clientes ADD INDEX(movil);
|
|
|
|
ALTER TABLE Consignatarios ADD INDEX(telefono);
|
|
ALTER TABLE Consignatarios ADD INDEX(movil);
|
|
|
|
ALTER TABLE Contactos ADD INDEX(Telefono);
|
|
ALTER TABLE Contactos ADD INDEX(Movil);
|
|
|
|
-- Returns all bad formated phones
|
|
|
|
SELECT telefono, movil c FROM Clientes
|
|
WHERE telefono NOT REGEXP '^[0-9]+$'
|
|
OR movil NOT REGEXP '^[0-9]+$'
|
|
UNION
|
|
SELECT telefono, movil FROM Consignatarios
|
|
WHERE telefono NOT REGEXP '^[0-9]+$'
|
|
OR movil NOT REGEXP '^[0-9]+$'
|
|
UNION
|
|
SELECT telefono, movil FROM Contactos
|
|
WHERE telefono NOT REGEXP '^[0-9]+$'
|
|
OR movil NOT REGEXP '^[0-9]+$';
|
|
|