Cómo reemplazar más de 2 palabras en el mismo registro?

[patriciodilet]

Hola! estoy intentando reemplazar 2 palabras que se extraen de una segunda tabla, todo con el comando REPLACE, pero solo logro reemplazar una palabra del registro, por ejemplo, tengo el registro "BALMACEDA 2355 LOC 102 B OF 25" y quiero reemplazar las palabras "LOC" y "OF" por "LOCAL" y "OFICINA", pero solo logro reemplazar la primera palabra, en este caso LOC por LOCAL.

Acá hay datos de prueba y la query que estoy usando:

Datos de prueba:

Código SQL:

CREATE TABLE #datos 
    (id INT PRIMARY KEY NOT NULL,
     direccion VARCHAR(250))
GO
INSERT INTO #datos VALUES (1,'MANUEL MONTT 1200 DPTO 3')
INSERT INTO #datos VALUES (2,'BALMACEDA 2355 LOC 102 B OF 25')
INSERT INTO #datos VALUES (3,'B OHGGINS 980 LOC')
INSERT INTO #datos VALUES (4,'LIB BDO OHIGGINS 3410 OF 48 LOC E')
INSERT INTO #datos VALUES (5,'V KENNEDY 9001 LOC 4')
INSERT INTO #datos VALUES (6,'VALLE AZAPA KM 36 PARC 5')
GO
CREATE TABLE #correcto
   (id INT PRIMARY KEY NOT NULL,
    nombre VARCHAR(250) NULL)
GO
INSERT INTO #correcto VALUES (1,'DEPARTAMENTO')
INSERT INTO #correcto VALUES (2,'LOCAL')
INSERT INTO #correcto VALUES (3,'OFICINA')
INSERT INTO #correcto VALUES (4,'KILOMETRO')
INSERT INTO #correcto VALUES (5,'PARCELA')
GO
CREATE TABLE #incorrecto
   (id INT PRIMARY KEY NOT NULL,
    nombre VARCHAR(250) NULL,
    id_correcto INT FOREIGN KEY REFERENCES #correcto(id) NOT NULL)
 
GO
INSERT INTO #incorrecto VALUES (1,'DPTO',1)
INSERT INTO #incorrecto VALUES (2,'LOC',2)
INSERT INTO #incorrecto VALUES (3,'OF',3)
INSERT INTO #incorrecto VALUES (4,'KM',4)
INSERT INTO #incorrecto VALUES (5,'PARC',5)
 

Y esta es la consulta que estoy utilizando para reemplazar las palabras:

Código SQL:

SELECT REPLACE (#datos.direccion, #incorrecto.nombre, #correcto.nombre) 
FROM  #correcto 
    INNER JOIN #incorrecto ON #correcto.id = #incorrecto.id_correcto 
    CROSS JOIN #datos   
    WHERE direccion LIKE '% ' + #incorrecto.nombre + '%'
 

Como pueden ver, extraigo las palabras que quiero reemplazar de una tabla "incorrecto" y obtengo la palabra bien escrita de "correcto", para luego realizar el replace, pero solo logro reemplazar una palabra por registro.

Cómo puedo hacerlo para reemplazar más de una palabra por registro?

Muchas gracias!

[iislas]

¿Por qué no utilizas el servicio de data cleaning?

http://msdn.microsoft.com/en-us/magazine/cc163731.aspx

[patriciodilet]

Iniciado por iislas

¿Por qué no utilizas el servicio de data cleaning?

[url]http://msdn.microsoft.com/en-us/magazine/cc163731.aspx[/url]

Muchas gracias por responder. Leí la información y me pareció increíble, no conocía esto.
Entendí que se usa a través de SSIS. He utilizado un poco de DTSx pero no encuentro la forma o las opciones para utilizar data cleaning. Me podrías orientar un poco por favor?
Y muchas muchas gracias!

[iislas]

Que versión y edición estas manejando

[patriciodilet]

Iniciado por iislas

Que versión y edición estas manejando

Uso SQL Server Management Studio 2005.
Atento a tus comentarios.

[iislas]

Debes instalar Visual Studio y el servicio de SSIS (Integration Services) de SQL Server 2005

Te falto mencionar que EDICION de SQL Server manejas

[patriciodilet]

Iniciado por iislas

Que versión y edición estas manejando

Uso SQL Server Management Studio 2005.

Perdón, uso Sql server Management Studio Versión 9 y tengo instalad visual studio 2010 y también SSIS.
Detalle:
Microsoft SQL Server Management Studio 9.00.5000.00
Herramientas cliente de Microsoft Analysis Services 2005.090.5000.00
Microsoft Data Access Components (MDAC) 6.1.7601.17514 (win7sp1_rtm.101119-1850)
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 9.10.9200.16844
Microsoft .NET Framework 2.0.50727.5477
Sistema operativo 6.1.7601

[damimg]

Iniciado por patriciodilet

Uso SQL Server Management Studio 2005.

Perdón, uso Sql server Management Studio Versión 9 y tengo instalad visual studio 2010 y también SSIS.
Detalle:
Microsoft SQL Server Management Studio 9.00.5000.00
Herramientas cliente de Microsoft Analysis Services 2005.090.5000.00
Microsoft Data Access Components (MDAC) 6.1.7601.17514 (win7sp1_rtm.101119-1850)
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 9.10.9200.16844
Microsoft .NET Framework 2.0.50727.5477
Sistema operativo 6.1.7601

Aclaro que datacleaning está disponible a partir de la versión 2012 de Sql Server

[Libras]

Código SQL:

SELECT REPLACE(REPLACE (#datos.direccion, #incorrecto.nombre, #correcto.nombre),#incorrecto.nombre,#correcto.nombre)
FROM  #correcto 
    INNER JOIN #incorrecto ON #correcto.id = #incorrecto.id_correcto 
    CROSS JOIN #datos   
    WHERE direccion LIKE '% ' + #incorrecto.nombre + '%'
 

ya probaste con algo asi??

[GeriReshef]

Probá este SQL dinámico:

Código SQL:

DECLARE @SQL VARCHAR(MAX);
WITH T AS
(SELECT COUNT(*) OVER() N,
        Cr.id,
        Cr.nombre CrNombre,
        InCr.nombre InCrNombre
FROM    #correcto Cr
INNER JOIN #incorrecto InCr
        ON Cr.id=InCr.id_correcto)
SELECT  @SQL='Replace('+IsNull(@SQL+',','direccion,')+''''+InCrNombre+''','''+CrNombre+''')'
FROM    T;
SELECT  @SQL='Select    *,'+CHAR(13)+CHAR(9)+CHAR(9)+@SQL+' direccion_corecto'+CHAR(13)+'From   #datos;';
Print   @SQL;
EXEC(@SQL);
 

[patriciodilet]

Iniciado por Libras

Código SQL:

SELECT REPLACE(REPLACE (#datos.direccion, #incorrecto.nombre, #correcto.nombre),#incorrecto.nombre,#correcto.nombre)
FROM  #correcto 
    INNER JOIN #incorrecto ON #correcto.id = #incorrecto.id_correcto 
    CROSS JOIN #datos   
    WHERE direccion LIKE '% ' + #incorrecto.nombre + '%'
 

ya probaste con algo asi??

Si, y quedan cosas raras, como por ejemplo la palabra LOC queda como LOCALA y debería ser LOCAL. Muchas gracias!

[patriciodilet]

Iniciado por GeriReshef

Probá este SQL dinámico:

Código SQL:

DECLARE @SQL VARCHAR(MAX);
WITH T AS
(SELECT COUNT(*) OVER() N,
        Cr.id,
        Cr.nombre CrNombre,
        InCr.nombre InCrNombre
FROM    #correcto Cr
INNER JOIN #incorrecto InCr
        ON Cr.id=InCr.id_correcto)
SELECT  @SQL='Replace('+IsNull(@SQL+',','direccion,')+''''+InCrNombre+''','''+CrNombre+''')'
FROM    T;
SELECT  @SQL='Select    *,'+CHAR(13)+CHAR(9)+CHAR(9)+@SQL+' direccion_corecto'+CHAR(13)+'From   #datos;';
Print   @SQL;
EXEC(@SQL);
 

Eres seco!!!! You're great!!
Funciona perfecto! esto mismo lo hago para normalizar todo el resto de la dirección (nombre de vía) y lo probé con tu método y funciona perfecto. Ahora solo necesito entender tu código, pero de verdad muchas gracias!

[patriciodilet]

Iniciado por GeriReshef

Probá este SQL dinámico:

Código SQL:

DECLARE @SQL VARCHAR(MAX);
WITH T AS
(SELECT COUNT(*) OVER() N,
        Cr.id,
        Cr.nombre CrNombre,
        InCr.nombre InCrNombre
FROM    #correcto Cr
INNER JOIN #incorrecto InCr
        ON Cr.id=InCr.id_correcto)
SELECT  @SQL='Replace('+IsNull(@SQL+',','direccion,')+''''+InCrNombre+''','''+CrNombre+''')'
FROM    T;
SELECT  @SQL='Select    *,'+CHAR(13)+CHAR(9)+CHAR(9)+@SQL+' direccion_corecto'+CHAR(13)+'From   #datos;';
Print   @SQL;
EXEC(@SQL);
 

Esto mismo me gustaría aplicarlo a los nombres de las vías. Pero tengo, por ejemplo, 100.000 nombres de vía incorrectos (o distintas formas de escribir la misma vía) asociados a 20.000 nombres de vía correctos.
Lo estoy intentando con esta query pero demora mucho...

[GeriReshef]

Iniciado por patriciodilet

Esto mismo me gustaría aplicarlo a los nombres de las vías. Pero tengo, por ejemplo, 100.000 nombres de vía incorrectos (o distintas formas de escribir la misma vía) asociados a 20.000 nombres de vía correctos.
Lo estoy intentando con esta query pero demora mucho...

El problema es que si existen 100,000 nombres incorrectos y otros 1,000,000 direcciones (en la tabla #datos) te resulta en 100 billones (100,000,000,000) de operaciones de Replace que en cada cual el sistema busca una subcadena dentro de una cadena.

Supongo que el sistema no utiliza mucho IO, pero sí - mucho CPU.

A partir de la versión 2012 existe la herramienta Data Quality Services que esta dedicada exactamente a estas tarreas (me parece que esto es el servicio que iislas mencionó en su respuesta). No tengo ninguna experiencia con ella.

Un último comentario: ¿porque los correctos e in correctos no estan en una tabla?
La relación entre las dos es 1:1...

[patriciodilet]

Iniciado por GeriReshef

El problema es que si existen 100,000 nombres incorrectos y otros 1,000,000 direcciones (en la tabla #datos) te resulta en 100 billones (100,000,000,000) de operaciones de Replace que en cada cual el sistema busca una subcadena dentro de una cadena.

Supongo que el sistema no utiliza mucho IO, pero sí - mucho CPU.

A partir de la versión 2012 existe la herramienta Data Quality Services que esta dedicada exactamente a estas tarreas (me parece que esto es el servicio que iislas mencionó en su respuesta). No tengo ninguna experiencia con ella.

Un último comentario: ¿porque los correctos e in correctos no estan en una tabla?
La relación entre las dos es 1:1...

Lamentablemente tengo disponible solo la versión 2005 de sql server.

Me equivoqué en el ejemplo, pueden haber 100 errores asociados a un correcto.