SQL Server - Como identificar las ocurrencias de un caracter especifico en una string o tabla

¡Hola, chicos!
¿Estás bien?

En esta publicación rápida, le demostraré cómo identificar las apariciones de un carácter específico en una cadena o tabla, es decir, contar cuántas veces aparece el carácter "X" en cada línea de una tabla.

Para los ejemplos a continuación, usaré 2 SP de CLR que demostré cómo crear (además de otras alternativas, para aquellos que no quieran usar CLR) en los artículos siguientes:

SQL Server: cómo exportar e importar archivos con datos tabulares (Ej.: CSV) usando CLR (C#)
Importación de archivos CSV a la base de datos de SQL Server
SQL Server: cómo importar archivos de texto a la base de datos (OLE Automation, CLR, BCP, BULK INSERT, OPENROWSET)

Hoy tuve un problema al importar un archivo CSV con más de 40 mil líneas y la rutina, que se ejecuta diariamente desde hace mucho tiempo sin problemas, me devolvió el siguiente mensaje de error.

IF (OBJECT_ID('tempdb..##Saida') IS NOT NULL) DROP TABLE ##Saida
EXEC CLR.dbo.stpImporta_CSV
    @Ds_Caminho_Arquivo = N'C:\Users\difil\Desktop\Teste.csv', -- nvarchar(max)
    @Ds_Separador = N'|', -- nvarchar(max)
    @Fl_Primeira_Linha_Cabecalho = 0, -- bit
    @Nr_Linha_Inicio = 0, -- int
    @Nr_Linhas_Retirar_Final = 0, -- int
    @Ds_Tabela_Destino = N'##Saida', -- nvarchar(max)
    @Ds_Codificacao = N'utf-8' -- nvarchar(max)

Mensaje 6522, Nivel 16, Estado 1, Procedimiento stpImporta_CSV, Línea 0 [Línea de inicio de lote 0] Se produjo un error de .NET Framework durante la ejecución de la rutina definida por el usuario o del agregado "stpImporta_CSV":
System.ApplicationException: Error: la matriz de entrada es mayor que el número de columnas de esta tabla.

Mensaje 6522, Nivel 16, Estado 1, Procedimiento stpImporta_CSV, Línea 0 [Línea de inicio de lote 0] Se produjo un error de .NET Framework durante la ejecución de la rutina definida por el usuario o del agregado “stpImporta_CSV”: System.ApplicationException: Error: la matriz de entrada es más larga que el número de columnas de esta tabla.

A partir de la descripción del error, quedó claro que había algún problema en mi CSV (probablemente una tubería, que es mi carácter separador en el archivo, en medio de las cadenas). Pensé en algunas soluciones que podría crear para identificar qué registro estaba incorrecto, como usar un cursor, un bucle while y hasta que se me ocurrió una solución MUY SIMPLE, rápida y extremadamente efectiva: nuestro viejo SELECT.

CREATE TABLE #dirceuresende (
    Ds_Texto VARCHAR(MAX)
)

INSERT INTO #dirceuresende
EXEC CLR.dbo.stpImporta_Txt 
    @caminho = N'C:\Users\difil\Desktop\Teste.csv' -- nvarchar(max)

SELECT *
FROM #dirceuresende

Ejemplo de CSV importado: 2 canales que separan nombre, edad y correo electrónico

Y ahora, con la consulta a continuación, podemos averiguar fácilmente qué registros tienen un número de tuberías diferente al del resto de líneas.

SELECT *, LEN(Ds_Texto) - LEN(REPLACE(Ds_Texto, '|', '')) AS Qt_Pipes
FROM #dirceuresende
WHERE LEN(Ds_Texto) - LEN(REPLACE(Ds_Texto, '|', '')) != 2

Después de eso, simplemente cambie el archivo, corrija las líneas e importe nuevamente (y eso es lo que hice en mi caso, donde solo 1 línea tenía un problema).

El problema de los espacios en blanco

En conversación con el Ariel Fernández, me recordó que cuando se usa la función LEN(), SQL Server aplica un RTRIM() a la cadena implícitamente, es decir, si hay espacios en blanco al final, estos espacios se cortarán en el cálculo. En la mayoría de los casos esto no tendrá ningún impacto, pero si el carácter separador que buscamos es exactamente el espacio " ", esto será un problema:

-- 5 espaços em branco no inicio e no final da string
DECLARE @String VARCHAR(100) = N'     Dirceu 29 email     '
SELECT (LEN(@String) - LEN(REPLACE(@String, ' ', '')))

Observe que la cadena de arriba tiene 5 espacios en blanco al principio y al final de la cadena, así como 2 más en el medio de la cadena que serían separadores. Al aplicar la función LEN(), los 5 caracteres en blanco finales se eliminan y el resultado final será 7 en lugar de 12.

Para solucionar este problema, podemos utilizar la función DATALENGTH, que devuelve el número de bytes en una cadena (LEN devuelve el número de caracteres). Con esto, nuestra consulta funciona correctamente con el ejemplo anterior:

-- 5 espaços em branco no inicio e no final da string
DECLARE @String VARCHAR(100) = N'     Dirceu 29 email     '
SELECT (DATALENGTH(@String) - DATALENGTH(REPLACE(@String, ' ', '')))

La función DATALENGTH y cadenas UNICODE

Esta solución parece haber solucionado nuestro problema, pero DATALENGTH tiene una peculiaridad cuando utilizamos datos Unicode (NCHAR, NVARCHAR, etc.), ya que estos tipos de datos registran información en formato de doble byte, es decir, se necesitan 2 bytes por cada carácter de la cadena. Como resultado, el resultado de la función DATALENGTH termina duplicándose para este tipo de datos, como se muestra a continuación:

Usando la función SQL_VARIANT_PROPERTY para identificar el tipo de variable

Una solución más definitiva a esto sería identificar el tipo de variable de entrada (o el tipo de columna) y si es unicode dividir el resultado de la longitud de datos por 2. Para poder identificar el tipo de datos de nuestra variable, usaremos la función SQL_VARIANT_PROPERTY():

DECLARE @String NVARCHAR(100) = N'     Dirceu 29 email     '

SELECT
    SQL_VARIANT_PROPERTY(@String, 'BaseType') AS [Base Type],
    SQL_VARIANT_PROPERTY(@String, 'Precision') AS [Precision],
    SQL_VARIANT_PROPERTY(@String, 'Scale') AS Scale,
    SQL_VARIANT_PROPERTY(@String, 'Collation') AS Collation,
    SQL_VARIANT_PROPERTY(@String, 'MaxLength') AS [MaxLength],
    SQL_VARIANT_PROPERTY(@String, 'TotalBytes') AS TotalBytes


DECLARE @String2 VARCHAR(100) = '     Dirceu 29 email     '

SELECT
    SQL_VARIANT_PROPERTY(@String2, 'BaseType') AS [Base Type],
    SQL_VARIANT_PROPERTY(@String2, 'Precision') AS [Precision],
    SQL_VARIANT_PROPERTY(@String2, 'Scale') AS Scale,
    SQL_VARIANT_PROPERTY(@String2, 'Collation') AS Collation,
    SQL_VARIANT_PROPERTY(@String2, 'MaxLength') AS [MaxLength],
    SQL_VARIANT_PROPERTY(@String2, 'TotalBytes') AS TotalBytes

Ahora, usando esta función para nuestras necesidades, podemos usarla para identificar el tipo de variable y realizar el cálculo correcto.

Consulta evaluando una cadena en variable:

-- 5 espaços em branco no inicio e no final da string UNICODE
DECLARE @String NVARCHAR(100) = N'     Dirceu 29 email     '
SELECT (DATALENGTH(@String) - DATALENGTH(REPLACE(@String, ' ', ''))) / (CASE WHEN SQL_VARIANT_PROPERTY(@String, 'BaseType') = 'nvarchar' THEN 2 ELSE 1 END)

-- 5 espaços em branco no inicio e no final da string
DECLARE @String2 VARCHAR(100) = '     Dirceu 29 email     '
SELECT (DATALENGTH(@String2) - DATALENGTH(REPLACE(@String2, ' ', ''))) / (CASE WHEN SQL_VARIANT_PROPERTY(@String2, 'BaseType') = 'nvarchar' THEN 2 ELSE 1 END)

Consulta usando una cadena en una tabla:

IF (OBJECT_ID('tempdb..#dirceuresende') IS NOT NULL) DROP TABLE #dirceuresende
CREATE TABLE #dirceuresende (
    Ds_Texto VARCHAR(4000)
)

INSERT INTO #dirceuresende
VALUES('     Dirceu 29 email      '), ('     Teste 30 email2      '), ('     Te ste 30 email2      ')


SELECT 
    *, 
    (DATALENGTH(Ds_Texto) - DATALENGTH(REPLACE(Ds_Texto, ' ', ''))) / (CASE WHEN SQL_VARIANT_PROPERTY(Ds_Texto, 'BaseType') = 'nvarchar' THEN 2 ELSE 1 END) AS Qt_Pipes
FROM
    #dirceuresende
WHERE
    (DATALENGTH(Ds_Texto) - DATALENGTH(REPLACE(Ds_Texto, ' ', ''))) / (CASE WHEN SQL_VARIANT_PROPERTY(Ds_Texto, 'BaseType') = 'nvarchar' THEN 2 ELSE 1 END) != 13

Identificando el registro que tiene más espacios que las otras líneas:

Función SQL_VARIANT_PROPERTY y cadenas con tamaño MAX

Incluso con la solución anterior, todavía tenemos un problema potencial. Si la cadena o columna es de tipo VARCHAR(MAX) o NVARCHAR(MAX), la función SQL_VARIANT_PROPERTY() presenta errores cuando se utiliza. En este caso, usted deberá identificar manualmente la necesidad de dividir el resultado entre 2 o no.

Ejemplo:

-- 5 espaços em branco no inicio e no final da string UNICODE
DECLARE @String NVARCHAR(MAX) = N'     Dirceu 29 email     '
SELECT (DATALENGTH(@String) - DATALENGTH(REPLACE(@String, ' ', ''))) / (CASE WHEN SQL_VARIANT_PROPERTY(@String, 'BaseType') = 'nvarchar' THEN 2 ELSE 1 END)

-- 5 espaços em branco no inicio e no final da string
DECLARE @String2 VARCHAR(MAX) = '     Dirceu 29 email     '
SELECT (DATALENGTH(@String2) - DATALENGTH(REPLACE(@String2, ' ', ''))) / (CASE WHEN SQL_VARIANT_PROPERTY(@String2, 'BaseType') = 'nvarchar' THEN 2 ELSE 1 END)

Resultado:

Mensaje 206, Nivel 16, Estado 2, Línea 3
Choque de tipos de operandos: nvarchar(max) es incompatible con sql_variant
Mensaje 206, Nivel 16, Estado 2, Línea 7
Choque de tipos de operandos: varchar(max) es incompatible con sql_variant

Espero que hayas disfrutado de este post tan sencillo y rápido y que esta idea te pueda ser útil algún día. Si desea saber más sobre las diferencias entre las funciones LEN() y DATALENGTH(), lea en esta publicación aquí. Está en inglés, pero es muy explicativo y completo.

¡Abrazos!

Cómo identificar las apariciones de un carácter específico en una cadena o tabla contar cuántos caracteres hay en la fila de la cadena

Dirceu Resende

Arquitecto de Bases de Datos y BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.

WhatsApp Telegram Ver mis certificaciones

Comentários (0)

Carregando comentários…