una - select replace sql

Cómo reemplazar varios caracteres en Access SQL?

Question

Soy un novato en SQL, así que espero que alguien pueda deletrear esto por mí. Intenté seguir la publicación "Reemplazar varias cadenas en SQL Query", pero me quedé atascado.

Estoy tratando de hacer lo mismo que el creador de la publicación anterior, pero con una tabla diferente y diferentes campos. Digamos que el siguiente campo " ShiptoPlant " en la tabla " BTST " tiene tres registros (mi tabla en realidad tiene miles de registros) ...

Nombre de tabla: BTST

   ---------------
   | ShiptoPlant |
   | ----------- |
   | Plant #1    |
   | Plant - 2   |
   | Plant/3     |
   ---------------

Esto es lo que intento escribir en la pantalla de SQL:

SELECT CASE WHEN ShipToPlant IN ("#", "-", "/") Then ""
ELSE ShipToPlant END FROM BTST;

Sigo recibiendo el mensaje (Error 3075) ...

"Syntax error (missing operator) in query expression 
''CASE WHEN ShiptoPlant IN (";","/"," ") Then "" ELSE ShipToPlant END''."

Quiero hacer esta operación para cada carácter en el teclado, con la excepción de "*" ya que es un comodín.

¡Cualquier ayuda que pueda proporcionar sería muy apreciada!

EDITAR: Información de fondo agregada de los comentarios

He recopilado los datos del nivel de factura de los artículos de línea de cada uno de nuestros 14 proveedores para el año calendario 2008. Estoy tratando de normalizar los nombres de las plantas que nos dan nuestros proveedores.

Cada proveedor puede llamar a una planta con un nombre diferente, por ejemplo

El servicio Signode en nuestra lista maestra podría ser llamado por los proveedores

Signode Service 
Signode - Service.
SignodeSvc
SignodeService

Estoy tratando de eliminar los caracteres no alfanuméricos para poder identificar la planta usando nuestra lista maestra creando una serie de enlaces que miren los primeros 10 caracteres, si no coinciden, 8 caracteres, 6, 4 ...

Mi problema básico es que no sé cómo quitar los caracteres alfanuméricos de la mesa. Haré esta operación en varias columnas, pero planeé crear consultas separadas para editar las otras columnas.

Quizás necesito hacer una consulta de actualización masiva que elimine todos los caracteres alfanuméricos. Aún no tengo claro cómo escribirlo. Aquí es con lo que comencé a sacar todos los espacios. Funcionó muy bien, pero falló cuando intenté anidar el reemplazo

UPDATE BTST SET ShipToPlant = replace(ShipToPlant," ","");

EDICION 2: información adicional tomada de los comentarios

Todos los meses, hasta 100 nuevas permutaciones de nuestros nombres de plantas aparecen en los datos de nuestra factura de partida: esto podría representar miles de registros de facturas. Estoy intentando construir una manera rápida y sucia de asignar un master_id del nombre definitivo a cada permutación de nombre de la planta. La mejor manera de hacerlo es mirando los campos de planta, dirección, ciudad y estado, pero el problema con esto es que estos campos también tienen varias permutaciones, por ejemplo,

128 Brookview Drive
128 Brookview Lane

Sacando alfanuméricos y haciendo

LEFT(PlantName,#chars) & _
LEFT(Address,#chars) & _
LEFT(City,#chars) & _
LEFT(State,#chars) 

y cambiando el número de caracteres hasta que se encuentre una coincidencia entre los datos de la factura y el Listado de plantas maestro (ambas tablas contienen los campos Planta, Dirección, Ciudad y Estado), puede encontrar una coincidencia. Por supuesto, cuando comienzas a disminuir la cantidad de caracteres que se LEFT , la exactitud se ve comprometida. He hecho esto en Excel y tuve un rendimiento decente. ¿Alguien puede recomendar una mejor solución?

Answer 1

Es posible que desee considerar una función definida por el usuario (UDF)

SELECT ShiptoPlant, CleanString([ShiptoPlant]) AS Clean
FROM Table


Function CleanString(strText)
Dim objRegEx As Object

Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.IgnoreCase = True
objRegEx.Global = True

objRegEx.Pattern = "[^a-z0-9]"
CleanString = objRegEx.Replace(strText, "")

End Function

Answer 2

No crea que Access admita la sentencia CASE. Considera usar iif:

iif ( condition, value_if_true, value_if_false )

Para este caso, puede usar la función REPLACE:

SELECT 
    REPLACE(REPLACE(REPLACE(yourfield, ''#'', ''''), ''-'', ''''), ''/'', '''') 
    as FieldName
FROM
    ....

Answer 3

Podría usar la función Replace incorporada dentro de Access

SELECT
    Replace(Replace(Replace(ShipToPlant, "#", ""), "-", ""), "/", "") AS ShipToPlant
FROM
    BTST

Como han dicho otros, dentro de Access puede escribir sus propias funciones en VBA y usarlas en sus consultas.

EDITAR:

Aquí hay una manera de manejar el límite de Reemplazo anidado envolviendo la función Reemplazar dentro de nuestra propia función. Se siente sucio pero funciona: ponlo en un módulo dentro de Access

Public Function SuperReplace(ByRef field As String, ByVal ReplaceString As String) As String
    '' Size this as big as you need... it is zero-based by default'' 
    Dim ReplaceArray(3) As String

    ''Fill each element with the character you need to replace''  
    ReplaceArray(0) = "#"
    ReplaceArray(1) = "-"
    ReplaceArray(2) = "/"
    ReplaceArray(3) = " "

    Dim i As Integer
    For i = LBound(ReplaceArray) To UBound(ReplaceArray)    
       field = Replace(field, ReplaceArray(i), ReplaceString)
    Next i

    SuperReplace = field    
End Function

Luego, pruébelo con esta consulta

SELECT 
    SuperReplace(ShipToPlant,"") AS ShipToPlant
FROM
    BTST

Es posible que desee tomar esto y expandirlo para que pueda pasar una serie de cadenas en lugar de codificarlas en la función.

EDICION 2:

En respuesta a la información adicional en los comentarios sobre la pregunta, aquí hay una sugerencia de cómo puede tratar la situación de manera diferente. La ventaja de este apprach es que una vez que haya asignado una permutación de nombre de la planta, no necesitará realizar una cadena de reemplazo en los datos futuros en los próximos años, solo agregue nuevos nombres de planta y permutaciones al mapa.

Comience creando otra tabla, llamémosla plant_map

CREATE TABLE plant_map (id AUTOINCREMENT PRIMARY KEY, name TEXT, master_id LONG)

en plant_map , agregue todas las permutaciones para nombres de plantas e inserte la identificación para el nombre que desea usar para referirse a un grupo de permutación de nombre de planta particular con, en el campo master_id. De sus comentarios, usaré Signode Service

INSERT INTO plant_map(name, master_id) VALUES ("Signode Service", 1);
INSERT INTO plant_map(name, master_id) VALUES ("Signode Svc", 1);
INSERT INTO plant_map(name, master_id) VALUES ("Signode - Service", 1);
INSERT INTO plant_map(name, master_id) VALUES ("Signode svc", 1);
INSERT INTO plant_map(name, master_id) VALUES ("SignodeService", 1);

Ahora cuando consulta la tabla BTST, puede obtener datos para el Servicio Signode usando

SELECT
    field1,
    field2
FROM
    BTST source
INNER JOIN
    (
    plant_map map1      
    INNER JOIN
    plant_map map2
    ON map1.master_id = map2.id
    )
    ON source.ShipToPlant = map1.name
WHERE
    map2.name = "Signode Service"

Los datos dentro de la tabla BTST pueden permanecer sin cambios.

Básicamente, esto se une al nombre de la planta en BTST con el nombre en plant_map luego, usando master_id , se une a sí mismo en id dentro de plant_map para que solo tenga que pasar un nombre "común". Aconsejaría poner un índice en cada uno de los name columnas y master_id en plant_map ya que ambos campos se usarán en uniones.

Answer 4

SELECT 
IIF
(
    Instr(1,ShipToPlant , "#") > 0 
    OR Instr(1,ShipToPlant , "/") > 0 
    OR Instr(1,ShipToPlant , "-") > 0, ""
    , ShipToPlant 
)
FROM BTST

Answer 5

Crea una función pública en un módulo de Código.

Public Function StripChars(ByVal pStringtoStrip As Variant, ByVal pCharsToKeep As String) As String

Dim sChar As String
Dim sTemp As String
Dim iCtr As Integer

  sTemp = ""

  For iCtr = 1 To Len(pStringtoStrip)
    sChar = Mid(pStringtoStrip, iCtr, 1)
    If InStr(pCharsToKeep, sChar) > 0 Then
      sTemp = sTemp & sChar
    End If
  Next

  StripChars = sTemp

End Function

Luego en tu consulta

SELECT
    StripChars(ShipToPlant, "abcdefghijklmnopqrstuvwxyz0123456789") AS ShipToPlantDisplay  
FROM 
    BTST

Notas: esto será lento para muchos registros. Si esto es permanente, cree una consulta de actualización con la misma función.

EDITAR: para hacer una actualización:

UPDATE BTST
    SET ShipToPlant = StripChars(ShipToPlant, "abcdefghijklmnopqrstuvwxyz0123456789")

Answer 6

Todo - Terminé anidando la función REPLACE () en dos consultas separadas. Como hay más de 35 caracteres no alfanuméricos que necesito reemplazar y Access limita la complejidad de la consulta a alrededor de 20 funciones anidadas, simplemente lo divido en dos procesos. Algo torpe, pero funcionó. Debería haber seguido el principio KISS en este caso. ¡Gracias por tu ayuda!

Answer 7

OK, su pregunta ha cambiado, por lo que la solución también lo hará. Aquí hay dos maneras de hacerlo. La manera rápida y sucia resolverá solo parcialmente su problema porque no podrá dar cuenta de las permutaciones más extrañas como espacios faltantes o palabras mal escritas. La manera rápida y sucia:

1. Crea una nueva tabla, llamémosla tChar.
2. Ponga un campo de texto en él - los caracteres que desea reemplazar - lo llamaremos " char para este ejemplo
3. Coloque todas las combinaciones de caracteres o caracteres que desee eliminar en esta tabla.

4. Crea y ejecuta la consulta a continuación. Tenga en cuenta que solo eliminará un elemento a la vez, pero también puede poner en él versiones diferentes del mismo reemplazo como ''-'' o ''-''. Para este ejemplo creé una tabla llamada tPlant con un campo llamado ShipToPlant .

   SELECT tPlant.ShipToPlant, Replace([ShipToPlant], (SELECT top 1 char FROM tChar WHERE instr(ShipToPlant,char)<>0 ORDER BY len(char) Desc),"" ) AS New FROM tPlant;

La mejor (pero mucho más compleja) forma. Esta explicación va a ser general porque sería casi imposible poner todo aquí. Si quieres contactarme directamente usa mi nombre de usuario en gmail .:

1. Cree una tabla de Calificadores: errores que las personas ingresan como svc en lugar de servicio. Aquí ingresarías cada permutación extraña que obtienes.
2. Crea una tabla con QualifierID y Plant ID. Aquí diría qué calificador va a qué planta.
3. Cree una consulta que una los dos y su tabla con los nombres de plantas erróneos. Use instr para decir lo que hay en los campos.
4. Crea una segunda consulta que agrave la primera. Cuente el campo instr y úselo como puntaje. La entrada con el puntaje más alto es la planta.
5. Tendrás que ingresar manualmente los que no puede encontrar, pero muy pronto serán casi insuperables ya que tienes más y más entradas en la tabla.

ughh

Tienes un par de opciones diferentes. En Access no hay CASE en sql, necesita usar IIF. No es tan elegante como las soluciones en los motores db más robustos y necesita ser anidado para esta instancia, pero hará el trabajo por usted.

SELECT
    iif(instr(ShipToPlant,"#")<>0,"",
    iif(instr(ShipToPlant,"-")<>0,"",
    iif(instr(ShipToPlant,"/")<>0,"",ShipToPlant ))) AS FieldName
FROM BTST;

También puede hacerlo usando sql para limitar sus datos.

SELECT YourID, nz(aBTST.ShipToPlant,"") AS ShipToPlant  
FROM BTST LEFT JOIN (
    SELECT YourID, ShipToPlant 
    FROM BTST 
    WHERE ShipToPlant NOT IN("#", "-", "/")
    ) as aBTST ON BTST.YourID=aBTST.YourID

Si conoce VB también puede crear sus propias funciones y ponerlas en las consultas ... pero esa es otra publicación. :) HTH

Answer 8

Sé que esta es una pregunta muy antigua, pero me encontré con ella mientras buscaba una solución a este problema, pero terminé usando un enfoque diferente.

El campo que deseo actualizar se llama ''Clientes''. Hay 20 caracteres acentuados en el campo ''Nombre de Cust'' para el que deseo eliminar los signos diacríticos, por lo que (por ejemplo) ã> a.

Para cada uno de estos caracteres creé una nueva tabla ''recodes'' con 2 campos ''char'' y ''recode''. ''char'' contiene el caracter que deseo eliminar, y ''recode'' aloja el reemplazo.

Luego, para el reemplazo hice una combinación externa completa dentro de la declaración de actualización

UPDATE Customers, Recodes SET Customers.CustName = Replace([CustName],[char],[recode]);

Esto tiene el mismo efecto que anidar todas las declaraciones de reemplazo, y es mucho más fácil de administrar.