Implementar la función split en T-SQL

- 3 minute read

En la anterior entrada vimos cómo concatenar datos de una columna en una fila. En esta entrada haremos justo lo contrario, dividir una cadena de texto en varios registros a partir de un carácter delimitador. Es decir, vamos a implementar en Transact-SQL la función split de la que disponen la mayoría de los lenguajes de programación.

Si buscamos en Google, encontraremos multitud de variantes de esta función. Sin embargo, la peculiaridad de este ejemplo es que no utiliza bucles y por lo tanto es más eficiente. Para conseguir esto, tenemos que utilizar una tabla auxiliar, concretamente la tabla spt_values de la base de datos master. Esta tabla contiene, entre otras cosas, 2048 registros con los números consecutivos del 0 al 2047 que son de gran utilidad para nuestro propósito.

El siguiente código muestra cómo crear la función con valor de tabla para que admite tres parámetros: la cadena que queremos separar, el carácter delimitador y el número de resultados que queremos que nos devuelva. Este último parámetro es opcional, así que si le pasamos el valor NULL como tercer parámetro, la función nos devolverá todos los registros.

CREATE FUNCTION StringSplit ( @String AS VARCHAR(2048), @Separator AS CHAR(1), @Count AS INT ) RETURNS TABLE AS RETURN ( 
  SELECT TOP (ISNULL(@Count, 2147483647)) SUBSTRING( @String, Number, CHARINDEX(@Separator, @String + @Separator, Number) - Number ) AS \[Substring\] 
  FROM master..spt\_values 
  WHERE \[Type\]=’P’ AND Number BETWEEN 1 AND LEN(@String) + 1 AND SUBSTRING(@Separator + @String, Number, 1) = @Separator )

La forma de utilizar esta función es la siguiente:

SELECT Substring FROM StringSplit('Lunes,Martes,Miércoles,Jueves,Viernes,Sábado,Domingo', ',', null);

Con la que obtendremos el siguiente resultado:

Substring
------------------------------------
Lunes
Martes
Miércoles
Jueves
Viernes
Sábado
Domingo

(7 filas afectadas)

Por último, sólo queda comentar que la limitación de esta función está en la propia tabla auxiliar (master..spt_values), ya que al contener sólo 2048 números consecutivos no puede tratar cadenas de texto más largas. Si tuviesemos que tratar tamaños superiores, deberíamos crear una tabla auxiliar con más números consecutivos.

Actualización (20/06/2010): Me he topado con otro artículo en el que se utilizan los métodos XML para crear la función split. La principal ventaja respecto al método que expliqué en el post es que no tiene la limitación de los 2048 carácteres y puede tratar cadenas de cualquier longitud. Aquí dejo el código que modifica la función StringSplit.

ALTER FUNCTION StringSplit
(
  @String NVARCHAR(MAX),
  @Separator CHAR(1),
  @Count AS INT
) RETURNS @t TABLE (\[Substring\] NVARCHAR(max))
AS
  BEGIN
  DECLARE @xml xml = N'\*' + replace(@String,@Separator,'**') + '*'

  INSERT INTO @t(\[Substring\])
  SELECT TOP (ISNULL(@Count, 2147483647))
  r.value('.','varchar(5)') as Value
  FROM @xml.nodes('//root/i') as records(r)

  RETURN
END
#programming