SQL Server, SQL Server 2012, Trucos

Tablas numéricas auxiliares (Tally Tables) en acción. Extracción de subcadenas mediante delimitadores

Después de explicar en el anterior artículo el modo de creación de tablas de dimensión para cubos de datos empleando tablas numéricas auxiliares, en esta ocasión continuaremos exponiendo situaciones de aplicación práctica relacionadas con el empleo de dichas tablas, abordando un problema frecuentemente resuelto mediante esta herramienta: la partición de una cadena en varias subcadenas a partir de un delimitador utilizado en la cadena principal.

Para lograr este objetivo el procedimiento a seguir consistirá en combinar la tabla numérica con la cadena; con ello averiguaremos respectivamente las posiciones de inicio y la longitud de cada subcadena, lo que nos permitirá extraerlas de la cadena principal.

En primer lugar necesitaremos crear una tabla numérica con base uno, es decir, que el   valor del primer registro sea el número 1 (ver un ejemplo en este enlace), conteniendo alrededor de 200 filas, cantidad suficiente para las pruebas a realizar, ya que la cadena con la que trabajaremos tendrá una longitud menor de dicha cifra.

Como primera aproximación al problema comenzaremos a combinar la tabla con la cadena para obtener un conjunto de resultados compuesto por sus caracteres independientes. Mediante la cláusula OFFSET…FETCH calcularemos cuántas filas de la tabla numérica necesitaremos emplear para realizar la extracción.


DECLARE @sTexto AS varchar(100);
SET @sTexto = 'monitor,teclado,ratón,impresora,smartphone';

SELECT NumeroID,
SUBSTRING(@sTexto, NumeroID, 1) AS Caracter
FROM Numeros
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) ROWS ONLY;

También podemos usar la cláusula TOP para averiguar la cantidad de filas a utilizar.


...
...
SELECT TOP(LEN(@sTexto))
...
...

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_01

 

Pero como acabamos de mencionar en un párrafo anterior, los datos esenciales que necesitamos para la extracción de las subcadenas son su posición de inicio y longitud. Para el primer caso utilizaremos una condición en la cláusula WHERE mediante la que conseguiremos que la consulta sólo devuelva las posiciones de la cadena en las que se halle un carácter delimitador, lo que nos servirá para calcular la posición de inicio de cada subcadena.


DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);

SET @sTexto = 'monitor,teclado,ratón,impresora,smartphone';
SET @sDelimitador = ',';

SELECT
NumeroID AS PosicionCaracterInicio,
SUBSTRING(@sTexto, NumeroID + 1, 1) AS CaracterInicio
FROM Numeros
WHERE SUBSTRING(@sTexto, NumeroID, 1) = @sDelimitador
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) ROWS ONLY;

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_02

 

El lector habrá comprobado que esta operación no proporciona la posición inicial de la primera subcadena debido a que el inicio (y el final) de la cadena principal carece del carácter delimitador.

No obstante, podemos solucionar este inconveniente desde la cláusula WHERE, concatenando en la función SUBSTRING el delimitador y la cadena, para obtener las posiciones de todos los delimitadores. Cuando esto ocurra, la consulta devolverá el mismo número de posición también a través de otra llamada a SUBTRING, pero aquí aplicaremos dicha posición solamente a la cadena principal, con lo que obtendremos el carácter de inicio de cada subcadena.


DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);

SET @sTexto = 'monitor,teclado,ratón,impresora,smartphone';
SET @sDelimitador = ',';

SELECT
NumeroID AS PosicionCaracterInicio,
SUBSTRING(@sTexto, NumeroID, 1) AS CaracterInicio
FROM Numeros
WHERE SUBSTRING(@sDelimitador + @sTexto, NumeroID, 1) = @sDelimitador
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) ROWS ONLY;

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_03

 

El siguiente diagrama muestra gráficamente la operativa de la consulta anterior.

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_04

 

En lo que respecta al cálculo para hallar la longitud de la subcadena, mediante la función CHARINDEX y la concatenación de las variables de la cadena y el delimitador   lograremos la posición del delimitador situado al final de cada subcadena. A este valor le restaremos la posición del carácter de inicio obteniendo la longitud, y por último uniremos todos estos elementos en la función SUBSTRING, obteniendo como resultado la subcadena que necesitamos.


DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);

SET @sTexto = 'monitor,teclado,ratón,impresora,smartphone';
SET @sDelimitador = ',';

SELECT
NumeroID AS PosicionCaracterInicio,
SUBSTRING(@sTexto, NumeroID, 1) AS CaracterInicio,
CHARINDEX(@sDelimitador, @sTexto + @sDelimitador, NumeroID) AS PosicionDelimitador,
CHARINDEX(@sDelimitador, @sTexto + @sDelimitador, NumeroID) - NumeroID AS LongitudSubcadena,
SUBSTRING(@sTexto, NumeroID, CHARINDEX(@sDelimitador, @sTexto + sDelimitador, NumeroID) - NumeroID) as Subcadena
FROM Numeros
WHERE SUBSTRING(@sDelimitador + @sTexto, NumeroID, 1) = @sDelimitador
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) ROWS ONLY;

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_05

 

A continuación podemos ver también un diagrama descriptivo del proceso.

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_06

 

Sin embargo, a pesar de su correcto funcionamiento, el código anterior sufre un problema de rendimiento originado por las concatenaciones realizadas entre las variables que contienen la cadena principal y el delimitador, tal y como Jeff Moden describe en su excelente artículo Tally OH! An Improved SQL 8K «CSV Splitter» Function, donde también se proponen algunas técnicas alternativas para conseguir particionar la cadena sin usar las mencionadas operaciones de concatenación

Una de estas técnicas consiste en utilizar una tabla numérica con base cero (primera fila con valor cero), gracias a la cual evitaremos la concatenación. Esta práctica, por contra, nos obligará a que en la cláusula WHERE, además de la condición para obtener las posiciones que correspondan a un delimitador, tengamos que añadir otra que compruebe los casos en que la columna NumeroID valga cero, ya que este será el criterio para poder calcular y obtener la primera subcadena de la cadena principal. No obstante, antes de realizar la extracción de subcadenas, utilicemos en primer lugar la consulta para analizar los diferentes elementos que obtenemos para la operación.


DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);
SET @sTexto = 'monitor,teclado,ratón,impresora,smartphone';
set @sDelimitador = ','

SELECT
NumeroID + 1 AS PosicionPrimerCaracter,
CHARINDEX(@sDelimitador, @sTexto, NumeroID + 1) AS PosicionDelimitador,
(CHARINDEX(@sDelimitador,@sTexto, NumeroID + 1)) - (NumeroID + 1) AS LongitudSubcadena
FROM Numeros
WHERE SUBSTRING(@sTexto, NumeroID, 1) = @sDelimitador
OR NumeroID=0
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) + 1 ROWS ONLY;

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_07

 

Observando los resultados obtenidos nos percataremos de que utilizando los cálculos realizados para averiguar las posiciones del primer carácter de la subcadena, el delimitador y la longitud; si intentamos extraer las subcadenas mediante las funciones SUBSTRING y CHARINDEX obtendremos un error provocado por los valores de la última fila del conjunto de resultados. Esto es debido a que el cálculo para obtener la longitud de la subcadena arroja un valor negativo por la inexistencia de carácter delimitador al final de la cadena principal, produciéndose un error en la función SUBSTRING al pasar este valor a su parámetro length.

Seguidamente vemos el código a utilizar para reproducir este error.


DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);
SET @sTexto = 'monitor,teclado,ratón,impresora,smartphone';
SET @sDelimitador = ','

SELECT
SUBSTRING(@sTexto, (NumeroID + 1), CHARINDEX(@sDelimitador, @sTexto, NumeroID + 1) - (NumeroID + 1)) AS Subcadena
FROM Numeros
WHERE SUBSTRING(@sTexto, NumeroID, 1) = @sDelimitador
OR NumeroID=0
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) + 1 ROWS ONLY;

Como vemos en la siguiente figura, las cuatro primeras subcadenas son obtenidas correctamente, produciéndose el error en la quinta.

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_08

 

Una forma de evitar el error (que no solucionarlo) consiste en aplicar la función ABS a la expresión utilizada para el parámetro length de la función SUBSTRING, convirtiendo a signo positivo el valor de la longitud de la subcadena.


SUBSTRING(
  @sTexto, 
  (NumeroID + 1), 
  ABS(CHARINDEX(@sDelimitador, @sTexto, NumeroID + 1) - (NumeroID + 1))
) AS Subcadena

Y como acabamos de mencionar, este pequeño truco no soluciona el problema, porque lo que hace no es calcular la longitud de la última subcadena, sino devolver un valor, que en este caso es igual a la posición de inicio de la misma, lo que en algunas ocasiones puede funcionar, pero si la longitud de esa última subcadena es lo suficientemente grande aparecerá truncada, como vemos en el siguiente ejemplo.

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_09

 

Es por ello que para averiguar la longitud de las subcadenas debemos emplear una estrategia distinta que nos permita calcular la última de ellas sin que ocurra este error, y hacerlo además de una forma que suponga la menor penalización posible de rendimiento en la ejecución de la consulta.

Aunque el error se produce en la función SUBSTRING al utilizar un valor negativo en el parámetro length, la raíz del problema reside en el cálculo de la posición del delimitador realizado mediante CHARINDEX, ya que la última subcadena carece de dicho carácter, y por lo tanto obtenemos un cero como resultado.

Vamos a intentar “engañar” a nuestra operación de cálculo del delimitador, de forma que cuando tenga que buscar dicho valor para la última subcadena, al no existir realmente tal delimitador, devolvamos 8000 como posición ficticia, que es el valor máximo del tipo de dato con el que estamos trabajando (varchar), y por consiguiente, la máxima longitud de cadena que utilizaríamos.

Esta operación la realizaremos en dos pasos: en primer lugar, cuando CHARINDEX devuelva el valor cero, mediante la función NULLIF devolveremos NULL; a continuación comprobaremos esta situación mediante la función ISNULL, y en caso afirmativo devolveremos 8000; si todo lo anterior no se cumple se ejecutará solamente CHARINDEX, lo cual ocurrirá para todas las subcadenas excepto la última.

De esta manera, al calcular la longitud de la subcadena nos aseguramos la obtención de un valor que no producirá error al ser utilizado en SUBSTRING, ya que si la suma de los valores para los parámetros start y length es mayor que la longitud de la cadena que estamos manejando, esta función devolverá la subcadena empezando desde el parámetro start hasta el final de la misma.


DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);
SET @sTexto = 'monitor,teclado,ratón,impresora,smartphone';
SET @sDelimitador = ',';

SELECT
SUBSTRING(
@sTexto,
NumeroID + 1,
ISNULL(
  NULLIF(CHARINDEX(@sDelimitador, @sTexto, NumeroID + 1 ), 0), 8000) - (NumeroID + 1)
) AS Subcadena
FROM Numeros
WHERE SUBSTRING(@sTexto, NumeroID, 1) = @sDelimitador
OR NumeroID = 0
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) + 1 ROWS ONLY;

Tras aplicar esta corrección al código obtendremos un conjunto de resultados compuesto por todas las subcadenas contenidas en la cadena principal.

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_10

 

Aunque ya hemos logrado optimizar la consulta eliminando las concatenaciones iniciales y solventando el error de cálculo para la longitud de las subcadenas, el operador OR también podría afectar negativamente al rendimiento, por lo que vamos a incorporar un nuevo ajuste a nuestro código, que elimine la necesidad de usar dicho operador.

Puesto que la utilidad de OR dentro del contexto en el que nos encontramos reside en la capacidad de permitirnos obtener la posición de inicio de la primera subcadena,  para conseguir el mismo resultado evitando el uso de este operador vamos a crear una expresión de tabla (CTE) compuesta por dos consultas unidas mediante el operador UNION. La primera devolverá el valor 1, mientras que la segunda será nuestra vieja conocida (sin OR) encargada de devolver las primeras posiciones de las subcadenas, en este caso a partir de la segunda subcadena en adelante.


DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);

SET @sTexto = 'monitor,teclado,ratón,impresora,smartphone';
SET @sDelimitador = ',';

WITH ctePosicionesInicio AS
(
  SELECT 1 AS PosicionInicio

  UNION ALL

  SELECT NumeroID + 1 AS PosicionInicio
  FROM Numeros
  WHERE SUBSTRING(@sTexto, NumeroID, 1) = @sDelimitador
  ORDER BY NumeroID
  OFFSET 0 ROWS
  FETCH FIRST LEN(@sTexto) + 1 ROWS ONLY
)
SELECT
SUBSTRING(
  @sTexto,
  PosicionInicio,
  ISNULL(NULLIF(CHARINDEX(@sDelimitador, @sTexto, PosicionInicio), 0), 8000) - PosicionInicio
) AS Subcadena
FROM ctePosicionesInicio;

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_11

El siguiente diagrama describe gráficamente la consulta que acabamos de crear.

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_12

 

Completada esta nueva mejora sobre nuestro código podemos dar por concluido su desarrollo, queda ahora por ver el modo de aplicarlo de la manera más práctica posible, ya que el hecho de copiar y pegar esta sentencia en aquellos lugares donde vayamos a necesitarla no parece el modo de uso más adecuado ni más sencillo de cara a su mantenimiento.

Una buena opción de cara a su reutilización sería incluir este código en una función con valor de retorno de tipo tabla (Inline Table-Valued Function) como vemos a continuación, lo que nos permitiría aplicarla sobre la columna de una tabla que contuviera cadenas con valores separados por un delimitador, de forma que el resultado de la partición de las cadenas podríamos emplearlo como entrada en la inserción de otra tabla.


CREATE FUNCTION ParticionarCadena(@sTexto AS varchar(100), @sDelimitador AS char(1))
RETURNS TABLE
AS
RETURN
(

WITH ctePosicionesInicio AS
(
  SELECT 1 AS PosicionInicio

  UNION ALL

  SELECT NumeroID + 1 AS PosicionInicio
  FROM Numeros
  WHERE SUBSTRING(@sTexto, NumeroID, 1) = @sDelimitador
  ORDER BY NumeroID
  OFFSET 0 ROWS
  FETCH FIRST LEN(@sTexto) + 1 ROWS ONLY
)
SELECT
SUBSTRING(
  @sTexto,
  PosicionInicio,
  ISNULL(NULLIF(CHARINDEX(@sDelimitador, @sTexto, PosicionInicio), 0), 8000) - PosicionInicio
) AS Subcadena
FROM ctePosicionesInicio

)

Como demostración del comentario anterior, en la base de datos que estemos utilizando para realizar estas pruebas crearemos una tabla con el nombre Cadenas, que contenga una columna identificadora de fila y otra con los valores concatenados y separados por coma de las columnas EmployeeKey, EmployeeNationalIDAlternateKey, FirstName, LastName, Title y DepartmentName, pertenecientes a la tabla DimEmployee de la base de datos AdventureWorksDW2012.


CREATE TABLE Cadenas
(
CadenaID int NULL,
Cadena varchar(200) NULL
)

--////
INSERT INTO Cadenas (CadenaID,Cadena)
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
CONVERT(varchar(4), EmployeeKey) + ',' +
EmployeeNationalIDAlternateKey + ',' +
FirstName + ',' +
LastName + ',' +
Title + ',' +
DepartmentName
FROM AdventureWorksDW2012..DimEmployee

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_13

 

Si queremos comprobar la ejecución de la función ParticionarCadena contra la tabla que acabamos de crear, combinaremos en una consulta dicha tabla junto a la llamada a la función utilizando el operador CROSS APPLY. En el lado derecho de la consulta situaremos la función pasándole como parámetro la columna Cadena de la tabla Cadenas y el delimitador a usar, mientras que en el lado izquierdo colocaremos las columnas de la tabla Cadenas, y la columna Subcadena devuelta por la función.


SELECT
C.CadenaID,
C.Cadena,
SubC.Subcadena
FROM Cadenas AS C
CROSS APPLY dbo.ParticionarCadena(C.Cadena,',') AS SubC

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_14

 

A continuación crearemos la tabla Subcadenas, que usaremos como destino de la operación de partición aplicada a la tabla Cadenas utilizando la función ParticionarCadena.


CREATE TABLE Subcadenas
(
Subcadena varchar(50) NULL
)

INSERT INTO Subcadenas (Subcadena)
SELECT SubC.Subcadena
FROM Cadenas AS C
CROSS APPLY dbo.ParticionarCadena(C.Cadena, ',') AS SubC

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_15

 

Y tras este ejemplo concluimos el presente artículo, en el que hemos podido comprobar nuevamente la enorme versatilidad de las tablas numéricas auxiliares o tally tables, desarrollando en esta ocasión un proceso que nos ha permitido tomar una cadena compuesta por subcadenas separadas por un carácter delimitador y extraer de forma independiente cada una de dichas subcadenas, esperamos que los ejemplos  aquí expuestos resulten de utilidad al lector.

1 Comentario

  1. anonymous

    Como continuación al conjunto de artículos dedicados a la aplicación práctica

Deja un comentario

Tema creado por Anders Norén