Este artículo explora la manipulación de cadenas a través la función coalesce en SQL Server.
La manipulación de cadenas es un proceso para poder generar otra forma de datos existentes de una forma que la empresa usa o muestra como resultados en los informes. He escrito antes artículos sobre la función de cadena SQL, incluidas las funciones de cadena SQL para la descripción general de la función Munging de datos y la subcadena de SQL que son acerca de la preparación de datos y las tareas de gestión de datos utilizando las funciones de cadena de SQL Server incorporadas.
El servidor SQL también tiene algunas funciones de caracteres incorporadas que también nos van a permitir manipular y transformar datos. Al mismo tiempo, es importante examinar el conjunto de datos, explorar valores de datos y codificar o decodificar los valores, según sea necesario, para generar datos significativos. Es muy importante saber cómo poder navegar a través de los valores perdidos en nuestros conjuntos de datos, comprender el impacto en los cálculos, consultas, informes y preparación de conjuntos de datos y poder desarrollar técnicas para evitar que los valores nulos arruinen nuestros conjuntos de resultados.
¿Qué es un valor NULO?
Antes de profundizar en cómo navegar por el campo minado potencial de los conjuntos de datos con valores faltantes y evitar pisar un valor Nulo, primero echemos un vistazo rápido a lo que significa NULO.
Según lo definido por Wikipedia
Nulo (o NULL) es un marcador especial usado en el lenguaje de consulta estructurado para indicar que no existe un valor de datos en la base de datos. Introducido por el creador del modelo de base de datos relacional, E. F. Codd, SQL Null sirve para cumplir el requisito de que todos los verdaderos sistemas de gestión de bases de datos relacionales (RDBMS) admitan una representación de “información faltante e información inaplicable”. Codd también introdujo el uso del símbolo omega (ω) griego en minúsculas para representar Nulo en la teoría de bases de datos. En SQL, NULL es una palabra reservada utilizada para identificar este marcador. … Esto no debe confundirse con un valor de 0. Un valor nulo indica la falta de un valor: la falta de un valor no es lo mismo que un valor de cero de la misma manera que la falta de una respuesta no es él Lo mismo que una respuesta de “no”.
Además …
SQL nulo es un estado, no un valor. Este uso es bastante diferente de la mayoría de los lenguajes de programación, donde el valor nulo de una referencia significa que no está apuntando a ningún objeto.
SQL proporciona algunas funciones muy útiles para poder trabajar con sus datos de caracteres en sus consultas SQL que describiremos en detalle
Función SQL Coalesce
Las funciones SQL Coalesce e IsNull se usan para manejar valores nulos. Durante el proceso de evaluación de expresiones, los valores nulos se reemplazan con el valor definido por el usuario.
La función SQL Coalesce evalúa todos los argumentos en orden y siempre devuelve el primer valor no nulo de la lista de argumentos definidos.
Sintaxis
COALESCE ( expresión [ 1…n ] )
Propiedades de la función SQL coalesce
- Las expresiones deben ser del mismo tipo de datos
- Puede contener múltiples expresiones
- La función SQL Coalesce es un atajo sintáctico para la expresión Case
- Siempre va a evaluar primero un entero, un entero seguido de una expresión de caracteres produce un entero como salida.
Ejemplos:
|
1 2 3 4 5 6 |
SELECT COALESCE (NULL,'A','B') SELECT COALESCE (NULL,100,20,30,40) SELECT COALESCE (NULL,NULL,20,NULL,NULL) SELECT COALESCE (NULL,NULL,NULL,NULL,NULL,'Prashanth') SELECT COALESCE (NULL,NULL,NULL,NULL,1,'Prashanth') SELECT COALESCE (NULL,NULL,NULL,NULL,NULL,'Prashanth',1) |
SQL Coalesce en una operación de concatenación de cadenas
En el siguiente ejemplo, vamos a concatenar algunos valores. Pero, de nuevo, es solo una revisión para informarle lo que sucede cuando tenemos un valor NULO. Entonces, sigamos adelante y vayamos a ejecutar el T-SQL. Y así podemos ver que encontramos un valor NULO al procesar la operación de concatenación de cadenas. El servidor SQL simplemente devuelve un NULO cada vez que encuentra un valor NULO. El resultado no es una combinación del nombre, nulo y apellido.
|
1 |
SELECT firstName +' '+MiddleName+' '+ LastName FullName FROM Person.Person |
Manejemos los valores nulos usando la función denominada SQL COALESCE. Esta función permite poder manejar el comportamiento del valor NULO. Por lo tanto, en este caso, use la función SQL coalesce para poder reemplazar cualquier valor NULO de segundo nombre con un valor ‘‘ (Char (13) -space). La instrucción SQL aún debe poder concatenar los tres nombres, pero no se mostrarán valores nulos en la salida. Ahora vemos que el nombre completo se muestra con un espacio en el medio, para valores NULO. De esta manera, es posible poder personalizar eficientemente los valores de columna.
|
1 |
SELECT firstName +' '+COALESCE(MiddleName,'') +' '+ LastName FROM Person.Person |
Función coalesce SQL y pivote
El siguiente ejemplo devuelve los valores que no son nulos concatenados de la tabla “estado”. En algunos casos, es posible que se deban asignar los valores estáticos concatenados a una variable. En tal caso, los valores de la columna de la ciudad se analizan utilizando la función Coalesce SQL y se concatenan dentro de una comilla simple para poder preparar una cadena de valores. La salida se manipula posteriormente para poder eliminar el último carácter para obtener una cadena válida de valor de entrada.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DROP TABLE IF EXISTS STATE; CREATE TABLE STATE ( CITY VARCHAR(50), STATE VARCHAR(500)) INSERT INTO STATE VALUES('Appleton','WI'),('Milwaukee','WI'),('Madison','WI'),('Miami','Florida'),('Jacksonville','Florida') DECLARE @col nvarchar(MAX); SELECT @col = COALESCE(@col,'') +''''+CITY +''''+ ',' FROM dbo.STATE WHERE state = 'WI'; SELECT substring(@col,1,len(@col)-1) |
Salida:
|
1 |
SELECT '('+substring(@col,1,len(@col)-1)+')' |
Función escalar definida por el usuario y función coalesce SQL
Se crea una función definida por el usuario para poder devolver una cadena específica a la entrada proporcionada y luego la salida se agrupa a través de una cláusula de agrupación. En el siguiente ejemplo, la función de valor escalar nos devuelve los valores de cadena concatenados separados por “,” para una entrada específica de “Ciudad”. En el siguiente ejemplo se devuelve una salida donde la columna de estado está agrupada y sus valores de ciudades están concatenados y separados por un delimitador ‘,’ (coma). También es posible usar STRING_AGG si está utilizando SQL Server 2017. Puede también es posible consultar más información con el artículo Funciones principales de cadena SQL en SQL Server 2017
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE FUNCTION dbo.tfn_CoalesceConcat ( @state varchar(100) ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @str NVARCHAR(MAX); SELECT @str = COALESCE(@str + ', ', '') + CITY FROM dbo.STATE WHERE state = @state ORDER BY state; RETURN (@str); END GO |
Así es como llamamos al nombre de la función dbo.tfn_CoalesceConcat con la instrucción select.
La salida resulta ser una secuencia concatenada de valores separados por un delimitador “,”
|
1 2 3 4 |
SELECT state, city = dbo.tfn_CoalesceConcat(state) FROM dbo.state GROUP BY state ORDER BY state; |
Validación de datos mediante la función coalesce SQL
En el siguiente ejemplo, se encontrarán los contactos de emergencia de los empleados. Por lo general, en cualquier organización, los números de teléfono del empleado se organizan y enumeran en las columnas de trabajo, casa, teléfono celular.
Veamos cómo encontrar a los empleados donde no se mencionan contactos de emergencia o, en otras palabras, saquemos todos los detalles del empleado con contactos de emergencia.
En el siguiente ejemplo, tb_EmergencyContact contiene a todos los números de contacto de todos los empleados.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
DROP TABLE IF EXISTS tb_EmergencyContact; CREATE TABLE tb_EmergencyContact ( empid int, firstname VARCHAR(100) NOT NULL, lastname VARCHAR(100) NOT NULL, relationship VARCHAR(100), homephone VARCHAR(25), workphone VARCHAR(25), cellphone VARCHAR(25) ); INSERT INTO tb_EmergencyContact ( empid, firstname, lastname, relationship, homephone, workphone, cellphone ) VALUES ( 1, 'Ambika', 'Prashanth', 'Wife', NULL, '920.176.1456', '928.132.2967' ),( 2, 'Prashanth', 'Jayaram', 'spouse', NULL, NULL, '982.132.2867' ), ( 3, 'Pravitha', 'Prashanth', 'Daughter', NULL, NULL, NULL ) |
La función SQL Coalesce se usa para poder seleccionar las columnas teléfono de casa, teléfono de trabajo y teléfono celular. En el caso de valores NULO, el valor “NA” (no aplicable), se devuelve una cadena literal.
|
1 2 3 4 5 6 |
SELECT firstname+''+lastname fullname, relationship, COALESCE(homephone, workphone, cellphone, 'NA') phone FROM dbo.tb_EmergencyContact |
Coalesce SQL y columnas calculadas
El siguiente ejemplo va a utilizar SQL COALESCE para comparar los valores de las columnas de salario por hora, salario y comisión y devolver solo el valor no nulo encontrado en las columnas.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE EMP (EMPNO INT NOT NULL, ENAME VARCHAR(20), JOB VARCHAR(10), MGR INT, JOINDATE DATETIME, HOURLYWAGE DECIMAL(7,2), SALARY DECIMAL(7, 2), COMMISSION DECIMAL(7, 2), NUMSALES DECIMAL(7,2), DNO INT) INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, '02-MAR-1970',NULL, 8000, NULL, 20,2), (7499, 'ALLEN', 'SALESMAN', 7698, '20-MAR-1971',NULL, 1600, 3000,4, 3), (7521, 'WARD', 'SALESMAN', 7698, '07-FEB-1983', 40,1250, 5000,10, 3); |
La siguiente consulta T-SQL se utiliza para enumerar el salario total pagado a todos los empleados
|
1 2 3 4 5 |
SELECT EMPNO,ENAME,CAST(COALESCE(HOURLYWAGE * 40 * 52, salary, Salary+(COMMISSION * NUMSALES)) AS decimal(10,2)) AS TotalSalary FROM dbo.EMP ORDER BY TotalSalary; |
Ahora, vamos a ver un ejemplo para poder crear una columna calculada con la función SQL Coalesce en SQL Server
De manera general, es posible que necesitemos usar la expresión en las tablas. En las tablas, se requiere poder calcular los valores ya que a menudo se calculan usando varias columnas existentes y con pocos valores escalares de la tabla. Además, estas columnas dependen de una o más columnas. De esta forma, podemos crear una columna calculada usando la función SQL Coalesce para que los valores nulos se manejen de manera eficiente.
|
1 2 3 4 5 6 7 |
ALTER TABLE dbo.EMP ADD Total_Salary AS CAST(COALESCE(HOURLYWAGE * 40 * 52, salary, Salary+(COMMISSION * NUMSALES)) AS decimal(10,2)) select * from EMP |
Ahora, es posible ver que una simple instrucción SELECT muestra los resultados calculados previamente.
SQL COALESCE y expresión CASE
La función SQL COALESCE se puede representar sintácticamente utilizando la expresión CASE. Como por ejemplo, como sabemos, la función Coalesce devuelve los primeros valores no nulos.
SELECT COALESCE (expresión1, expresión2, expresión3) FROM TABLENAME;
La declaración SQL de la fusión anterior puede reescribirse usando la declaración CASE.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT firstname+''+lastname fullname, relationship, CASE WHEN homephone is NOT NULL Then homephone WHEN cellphone is NOT NULL Then cellphone WHEN workphone is NOT NULL Then workphone ELSE 'NA' END EmergencyContactNumber FROM dbo.tb_EmergencyContact |
La consulta devuelve el mismo resultado que el que usa la función COALESCE.
Conclusión
En este artículo, discutimos algunos consejos y trucos para poder mostrar el uso de la función SQL Coalesce para consultar efectivamente con T-SQL. También discutimos varios casos de uso de la función SQL Coalesce. También es posible optimizar la salida creando una columna calculada.
Espero que este artículo sobre la función Coalesce en SQL Server haya sido entretenido. Siéntase libre de realizar cualquier pregunta en los comentarios a continuación.
Mi especialidad es el diseño y la implementación de soluciones de alta disponibilidad y la migración de bases de datos multiplataforma. Las tecnologías en las que trabajo actualmente son SQL Server, PowerShell, Oracle y MongoDB.
Ver todas las publicaciones de Prashanth Jayaram
- Descripción general de la función SQL CAST y SQL CONVERT - December 6, 2019
- Revisión del operador relacional y descripción general dePivot y Unpivot estático y dinámico de SQL - November 6, 2019
- Revisión, ejemplos y uso de SQL Union - November 4, 2019