separar - ¿Cuál es la mejor manera de identificar los caracteres ocultos en el resultado de una consulta en SQL Server(Analizador de consultas)?

sql substring hasta un caracter

Question

Al intentar identificar datos erróneos (que a menudo requieren revisión y eliminación manual), me gustaría una forma fácil de ver los caracteres ocultos, como TAB, Espacio, Retorno de carro y Avance de línea. ¿Hay una forma incorporada para esto?

En una pregunta similar aquí sobre stackoverflow, con respecto a Oracle, se sugirió una función DUMP (fieldname), pero no sé si eso facilitaría las cosas incluso si existiera una función correspondiente en SQL Server, ya que necesito ver los caracteres en su contexto.

La mejor idea que se me ocurrió fue reemplazar los caracteres ocultos esperados por otros visibles, como este:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(myfield, '' '', ''˙''), CHAR(13), ''[CR]''), CHAR(10), ''[LF]''), CHAR(9), ''[TAB]'') FROM mytable

¿Hay alguna manera mejor? No me gusta de esta manera, ya que podría haber otros caracteres ocultos menos comunes que yo no tenga en cuenta, como TAB vertical, etc. Encendiendo "mostrar caracteres ocultos", como puede hacer en casi cualquier editor de texto, Sería una buena característica en el Analizador de consultas de SQL Server, por lo que casi espero que también se pueda hacer en el servidor SQL ... o al menos que alguien tenga una idea aún mejor que la mía, para mostrar este tipo de espacio en blanco. información

Acabo de darme cuenta de que hay una forma integrada de ver "espacio en blanco", no en el Analizador de consultas SQL, sino en la parte de la interfaz que una vez fue el administrador de SQL Enterprise. Haga clic con el botón derecho en una tabla en el árbol del Explorador de objetos de SQL Management Studio y seleccione "Editar las 200 filas principales". En el resultado, el espacio en blanco (al menos CR LF) es visible como cuadrados vacíos.

Answer 1

He enfrentado el mismo problema con un carácter que nunca logré hacer coincidir con una consulta donde CHARINDEX, LIKE, REPLACE , etc. no funcionó. Luego he usado una solución de fuerza bruta que es horrible, pesada pero funciona:

Paso 1 : haga una copia del conjunto de datos completo: realice un seguimiento de los nombres originales con un source_id que haga referencia al pk de la tabla de origen (y mantenga este ID de origen en todas las tablas siguientes). Paso 2 : LTRIM RTRIM los datos, y reemplaza todos los espacios dobles, tabulaciones, etc. (básicamente todos los caracteres CHAR (1) a CHAR (32) por un espacio. LTRIM RTRIM todo el conjunto. sabes (obtén la lista de todas las citas, comillas dobles, etc.) por algo de az (sugiero z). Básicamente, reemplaza todo lo que no sea un inglés estándar por az (usando REEMPLAZAR el REEMPLAZO de REEMPLAZO en un bucle). Paso 4 : dividida por palabra en una segunda copia, donde cada palabra está en una fila separada (la división es una SUBSTRING basada en la posición de los caracteres del espacio). En este punto, deberíamos pasar por alto aquellos en los que hay un espacio oculto que hicimos. Paso 5 : divida cada palabra en una tercera copia, donde cada letra está en una fila separada (sé que es una tabla muy grande): lleve un registro del índice de caracteres de cada letra en una columna separada. Paso 6 : Seleccione todo en la tabla anterior que no sea LIKE [az]. Esta es la lista de los caracteres no identificados que queremos excluir. ude

A partir de la salida del paso 6, tenemos datos suficientes para hacer una serie de subcadenas de la fuente para seleccionar todo menos el carácter desconocido que queremos excluir.

Nota 1 : hay formas inteligentes de optimizar esto, dependiendo del tamaño de la expresión original (los pasos 4, 5 y 6 se pueden hacer de una sola vez).

Nota 2 : esto no es muy rápido, pero es la forma más rápida de hacer esto para un conjunto de datos de gran tamaño, porque la división de líneas en palabras y palabras en letras se realiza mediante subcadena, que divide toda la tabla en secciones de un carácter. Sin embargo, esto es bastante pesado para construir. Con un conjunto más pequeño, puede ser suficiente analizar cada registro uno por uno y buscar un carácter que no esté en una lista de todos los caracteres en inglés más todos los caracteres especiales.

Answer 2

La forma en que lo hice fue seleccionando todos los datos.

select * from myTable y luego haga clic con el botón derecho en el conjunto de resultados y seleccione "Guardar resultados como ..." en un archivo csv.

Abriendo el archivo csv en Notepad ++ Vi que los caracteres LF no estaban visibles en el conjunto de resultados de SQL Server.

Answer 3

Para encontrarlos, puedes usar este

;WITH cte AS
(
   SELECT 0 AS CharCode
   UNION ALL
   SELECT CharCode + 1 FROM cte WHERE CharCode <31
)
SELECT
   *
FROM
   mytable T
     cross join cte
WHERE
   EXISTS (SELECT *
        FROM mytable Tx
        WHERE Tx.PKCol = T.PKCol
             AND
              Tx.MyField LIKE ''%'' + CHAR(cte.CharCode) + ''%''
         )

Reemplazar el EXISTS con un JOIN le permitirá REEMPLAZARlos, pero obtendrá varias filas ... No puedo pensar en una forma de evitar eso ...

Answer 4

Podría crear una función que aborde todas las posibilidades de espacios en blanco y habilitar solo las que parezcan apropiadas:

SELECT dbo.ShowWhiteSpace(myfield) from mytable

Descomente solo aquellos casos de espacios en blanco que desea probar:

CREATE FUNCTION dbo.ShowWhiteSpace (@str varchar(8000))
RETURNS varchar(8000)
AS
BEGIN
     DECLARE @ShowWhiteSpace varchar(8000);
     SET @ShowWhiteSpace = @str
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(32), ''[?]'')
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(13), ''[CR]'')
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(10), ''[LF]'')
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(9),  ''[TAB]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(1),  ''[SOH]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(2),  ''[STX]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(3),  ''[ETX]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(4),  ''[EOT]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(5),  ''[ENQ]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(6),  ''[ACK]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(7),  ''[BEL]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(8),  ''[BS]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(11), ''[VT]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(12), ''[FF]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(14), ''[SO]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(15), ''[SI]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(16), ''[DLE]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(17), ''[DC1]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(18), ''[DC2]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(19), ''[DC3]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(20), ''[DC4]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(21), ''[NAK]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(22), ''[SYN]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(23), ''[ETB]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(24), ''[CAN]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(25), ''[EM]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(26), ''[SUB]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(27), ''[ESC]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(28), ''[FS]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(29), ''[GS]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(30), ''[RS]'')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(31), ''[US]'')
     RETURN(@ShowWhiteSpace)
END

Answer 5

Siempre puede usar la función DATALENGTH para determinar si tiene caracteres de espacio en blanco adicionales en los campos de texto. Esto no hará que el texto sea visible, pero le mostrará dónde hay caracteres de espacio en blanco adicionales.

    SELECT DATALENGTH(''MyTextData '') AS BinaryLength, LEN(''MyTextData '') AS TextLength

Esto producirá 11 para BinaryLength y 10 para TextLength.

En una tabla a su SQL le gustaría esto:

    SELECT * 
    FROM tblA
    WHERE DATALENGTH(MyTextField) > LEN(MyTextField)

Esta función se puede utilizar en todas las versiones de SQL Server a partir de 2005.

Answer 6

select myfield, CAST(myfield as varbinary(max)) ...