SQL Server - Eliminar cualquier caracter excepto 0-9 y símbolo +

   
Vista:

Eliminar cualquier caracter excepto 0-9 y símbolo +

Publicado por Pedro (8 intervenciones) el 02/09/2015 18:30:59
Buenas tardes,

en la base de datos de mi ayuntamiento tenemos el campo "Teléfono1" y "Teléfono2" y los queremos normalizar para que sólo muestre números del 0 al 9 y, a lo sumo, el símbolo "+" de los teléfonos extranjeros. Entiendo que con el ejemplo para "Teléfono1", con cambiar luego el 1 por el 2, la sentencia es la misma.

El normalizarlo se debe a que, con el paso del tiempo, ha habido gente que ha puesto espacios o puntos entre los números al introducirlos, o alguna anotación del teléfono ("madre", "padre", etc.).

Tengo estas sentencias que me solucionan parte del problema:

select telefono1 from FiscalPolicia where Telefono1 not like '%[a-z]%' and Telefono1 like '% %' order by Telefono1
Con esta veo los teléfonos de los ciudadanos donde el teléfono no tenga ninguna letra y que tengan un espacio entre medias, es decir, nos quitamos de encima los que tiene puesto Madre o padre, por ejemplo.

select telefono1, codigofiscal from fiscalpolicia where telefono1 like '%[a-z]%' order by Telefono1
Con esta veo los que tienen alguna letra en el campo teléfono.

select replace(telefono1,' ',''), codigofiscal from fiscalpolicia where telefono1 not like '%[a-z]%' and Telefono1 like '% %' order by Telefono1
Con esta, veo como queda si lo modificamos. Quita espacios y muestra todo junto.

update FiscalPolicia set Telefono1 = replace(telefono1,' ','') where telefono1 not like '%[a-z]%' and Telefono1 like '% %'
Con esta aplico el SELECT REPLACE de arriba.


Aunque sería un apaño que solucionaría gran parte del problema, busco una sentencia que lo haga todo con un sólo UPDATE. La idea: que cualquier carácter distinto del 0 al 9 y el símbolo MÁS (si el "+" lo complica, podemos eliminarlo también y dejar sólo los números) sea eliminado.
Una propuesta, pero que no sirve por lo visto para SQL Server 2008, era esta: update fiscalpolicia set telefono1=replace(telefono1, [^0-9+],'') where telefono1<>replace(telefono1, [^0-9+],'')

Si pudiera ser también posible tener los SELECT para ver los resultados antes de aplicar UPDATE, sería ideal.

Mil gracias de antemano por vuestra atención.
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
Imágen de perfil de Isaias

Eliminar cualquier caracter excepto 0-9 y símbolo +

Publicado por Isaias (3180 intervenciones) el 02/09/2015 19:31:15
Para SQL Server 2008, tienes un servicio que se llama DATA CLEANING, donde defines tus modelos de limpieza y los ejecutas, cada que tu quieras.

Ahora bien, ese es un "mejoral", tu necesitas modificar tu aplicativo, el que captura los teléfonos, este, debería aplicar tus reglas de negocio.

Si bien se puede hacer mediante una función regular:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
create function dbo.NumberClean
(@String_in nvarchar(100))
returns bigint
as
begin
declare @NumberStr nvarchar(100), @Number_out bigint
 
;with Chars (Seq, Chr) as
	(select number, substring(@string_in, number, 1)
	from dbo.Numbers
	where number between 0 and len(@string_in))
 
	select @numberstr = coalesce(@numberstr + chr, chr)
	from chars
	where chr like '[0-9]'
 
	select @number_out = @numberstr
 
return @number_out
end

O bien, mediante t-sql:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
declare @pos smallint
declare @string varchar(100)
 
set @string = '1109A><":{$%^&*4DSE2@!~$%^&567KJHGT'
 
while isnumeric(@string+'e0') = 0
begin
 
set @pos = (select patindex('%[^0-9]%',@string))
set @string = (select replace(@string,substring(@string,@pos,1),''))
 
end
 
select @string

Insisto que esto debería resolverlo la capa "cliente"
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Isaias

Eliminar cualquier caracter excepto 0-9 y símbolo +

Publicado por Isaias (3180 intervenciones) el 02/09/2015 19:49:04
Tal vez quieras hacerlo de forma dinámica
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE FUNCTION [dbo].[fn_StripCharacters]
(
    @String NVARCHAR(MAX),
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'
 
    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
 
    RETURN @String
 
END
 
 
--Alphabetic only:
SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z')
 
 
--Numeric only:
SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^0-9')
 
 
--Alphanumeric only:
SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z0-9')
 
 
--Non-alphanumeric:
SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', 'a-z0-9')
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Eliminar cualquier caracter excepto 0-9 y símbolo +

Publicado por Pedro (8 intervenciones) el 02/09/2015 19:55:08
Gracias de antemano por tu temprana respuesta!!
No puedo mejorar mi aplicativo porque es un programa privativo comprado externamente a cuyo desarrollo, como usuario, puedo aportar ideas o mejoras que adoptan o no, pero en este caso, su política es que en caso de conflicto (que unos lo quieran "cerrar" y sólo permitir números y sin espacios, y otros lo quieren abierto) toman la opción menos restrictiva (campos alfanuméricos de 15 caracteres). Y frente a eso...Lo que ya no sé es, si me meto en SQL Server y voy a la tabla FiscalPolicia, y me voy al campo "Teléfono1" y "Teléfono2", si puedo cambiar la configuración para que sólo se pueda poner números.

Por otro lado, quisiera pedirte disculpas de antemano, pero no tengo ni idea de cómo se aplica ni apenas nada de cómo funciona el programa y no sé como lo tengo que hacer. Mis conocimientos son mínimos en SQL Server (el programa privativo sí que lo domino muy bien) y hasta ahora, lo poquito que he hecho, han sido órdenes simples como Update o Select similares a los del ejemplo. ¿Lo copio y pego en una nueva consulta y ya se aplica sólo? Por otro lado, no veo por ningún sitio el campo Teléfono1 ni la tabla....¿me tengo que ir a él y ejecutarle para ese sólo? (tengo un entorno de pruebas, pero copiando y pegando

La opción de automatizar me ha gustado mucho y también me gustaría saber cómo se podrían programar sentencias que ya tengo hechas para mantenimiento de la BD, pero el punto anterior y este no sé si es tema de otro post o me puedes facilitar algún enlace a un tutorial de iniciación porque me todo me suena a chino :_(

Igualmente, muy agradecido por tu atención. De verdad!
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Isaias

Eliminar cualquier caracter excepto 0-9 y símbolo +

Publicado por Isaias (3180 intervenciones) el 04/09/2015 18:37:39
Si no tienes ni idea en que tabla y campo están los teléfonos, estamos perdidos, ya que no sabríamos donde aplicar el query que te mande (olvida de las funciones).

En cuanto a proceso de limpiar los datos, lee este articulo

https://msdn.microsoft.com/es-es/library/gg524800.aspx
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar