Usando la Función String_split
Microsoft SQL Server en la versión 2017 ha incluído algunas funciones interesantes para el manejo de cadenas de texto, una de ellas es la función String_agg, la que permitía convertir un conjunto de valores de cadena a una cadena separada por un caracter. En este artículo se explica como se usa la función String_Split que hace lo inverso de la función String_agg.
Ver Función String_agg
Nivel de compatibilidad necesario
Para poder usar la función String_split es necesario el nivel de compatibilidad mínimo de 130, que corresponde a SQL Server 2016.
Ver Compatibilidad de base de datos en SQL Server
Función String_split
Es una función de valor de tabla que separa una cadena de caracteres en partes basado en un separador.
Sintaxis
String_split(Cadena, Separador)
Donde
Cadena
Es la cadena de caracteres a separar
Separador
Caracter que se usa como base para separar. El caracter es simple y puede ser nvarchar(1), varchar(1), nchar(1) o char(1)
La imagen ilustra el resultado de la función String_split
Ejercicios
Ejercicio 1
Convertir los nombres en un valor de columna
Declare @Personal nvarchar(100) = ‘Carolina, Maria Fernanda, Aracely, Esmeralda, Rossana, Silvia’
Select value from String_split(Replace(@Personal,’, ‘,’,’),’,’) As Personas
go
El resultado se muestra en la siguiente imagen
Ejercicio 2
Convertir los Productos en un valor de columna
Declare @Productos nvarchar(100) = ‘Monitor, Teclado, Mouse, Disco duro, Impresora, UPS, Scanner’
Select value from String_split(Replace(@Productos,’, ‘,’,’),’,’)
go
El resultado se muestra en la siguiente imagen
Usando la base de datos Northwind
use Northwind
go
Ejercicio 3
Crear una vista con las categorías y sus productos separados por coma. Esta vista se va a usar para luego presentar los productos de cada una de las categorías como una columna.
Create view vistaCategoriasProductosComa
As
select C.CategoryID As ‘Código’,
C.CategoryName As ‘Categoría’,
Productos = STRING_AGG(P.ProductName,’,’)
within group (order by P.ProductName asc)
from Categories As C
join Products As P on C.CategoryID = P.CategoryID
group by C.CategoryID, C.CategoryName
go
Los registros en la vista
select * from vistaCategoriasProductosComa
go
El resultado se muestra en la siguiente imagen
Ahora vamos a separar los productos cada uno en filas.
select V.Código, V.Categoría, value As Producto
from vistaCategoriasProductosComa As V
cross apply string_split(V.Productos,’,’)
go
Ver Cross Apply en SQL Server
El resultado se muestra en la siguiente imagen
Obtener solamente los productos
select value As Producto
from vistaCategoriasProductosComa As V
cross apply string_split(V.Productos,’,’)
go
El resultado se muestra en la siguiente imagen
Ordenamiento de los valores
Al aplicar el ordenamiento de valores obtenidos de una cadena separada por coma, debe considerar que a partir del segundo valor puede contener un espacio en blanco, se debe eliminar el espacio en blanco usando la función Replace para reemplazar los caracteres ‘, ‘ (coma y espacio) por solamente la coma.
Ejercicio 4
Listar los productos ordenados en forma ascendente.
Declare @Productos nvarchar(100) = ‘Monitor, Teclado, Mouse, Disco duro, Impresora, UPS, Scanner’
Select rtrim(value) As Productos from String_split(Replace(@Productos,’, ‘,’,’),’,’)
order by value
go
El resultado se muestra en la siguiente imagen
Filtrando los valores
Para realizar el filtrado de los valores usando el campo con los valores a separar, se usar la palabra reservada Value para hacer referencia al conjunto de valores.
Ejercicio 5
Para mostrar los productos que inician con la letra M.
Declare @Productos nvarchar(100) = ‘Monitor, Teclado, Mouse, Disco duro, Impresora, UPS, Scanner’
Select rtrim(value) from String_split(Replace(@Productos,’, ‘,’,’),’,’)
where Upper(value) like ‘M%’
order by value
go
El resultado se muestra en la siguiente imagen
Ejercicio 6
Mostrar los productos que inician con las letras C, F y M
select V.Código, V.Categoría, value As Producto
from vistaCategoriasProductosComa As V
cross apply string_split(V.Productos,’,’)
where upper(value) like ‘C%’ or upper(value) like ‘F%’ or
upper(value) like ‘M%’
Order by value
go
Ver Cross Apply en SQL Server
El resultado se muestra en la siguiente imagen
Evitando valores en blanco
Al generar la lista de valores puede que algunos de ellos no tengan datos, puede evitarse posiblemente de acuerdo a las reglas de negocio usando restriciones de tipo Default.
Ver Usando Restricciones en SQL Server
Ejercicio 7
Listado de productos, algunos con valores en blanco.
Declare @Productos nvarchar(100) = ‘Monitor, Teclado, , , Mouse, Disco duro, , , Impresora, UPS, Scanner’
Select rtrim(value) from String_split(Replace(@Productos,’, ‘,’,’),’,’)
go
El resultado se muestra en la siguiente imagen
Evitando los valores en blanco.
Declare @Productos nvarchar(100) = ‘Monitor, Teclado, , , Mouse, Disco duro, , , Impresora, UPS, Scanner’
Select rtrim(value) from String_split(Replace(@Productos,’, ‘,’,’),’,’)
where RTRIM(value) <> »
go
El resultado se muestra en la siguiente imagen
Generar una tabla con los valores obtenidos con String_Split
Se puede usar la opción Into de la instrucción Select para generar una tabla con los valores obtenidos usando String_split
Ejercicio 8
Crear una tabla con los productos de la categoría 3, usando la vista creada en el ejercicio 3.
select value As Producto
into ProductosCategoria3
from vistaCategoriasProductosComa As V
cross apply string_split(V.Productos,’,’)
where v.Código = 3
go
Ver los productos
select * from ProductosCategoria3
go
El resultado se muestra en la siguiente imagen
