Uso de funciones escalares
Las funciones escalares devuelven un valor único y normalmente funcionan en una sola fila de datos. El número de valores de entrada que toman puede ser cero (por ejemplo, GETDATE), uno (por ejemplo, UPPER) o varios (por ejemplo, ROUND). Dado que las funciones escalares siempre devuelven un solo valor, se pueden usar en cualquier lugar en el que se necesite un único valor (el resultado). Se usan normalmente en cláusulas SELECT y predicados de cláusula WHERE. También se pueden usar en la cláusula SET de una instrucción UPDATE.
Las funciones escalares integradas se pueden organizar en muchas categorías, como cadena, conversión, lógica, matemática y otras. En este módulo se explican algunas funciones escalares comunes.
Estas son algunas de las consideraciones que hay que tener en cuenta al usar funciones escalares:
- Determinismo: si la función devuelve el mismo valor para el mismo estado de entrada y base de datos cada vez que se llama, se dice que es determinista. Por ejemplo, ROUND(1.1, 0) siempre devuelve el valor 1.0. Muchas funciones integradas son no deterministas. Por ejemplo, GETDATE() devuelve la fecha y hora actuales. Los resultados de las funciones no deterministas no se pueden indexar, lo que afecta a la capacidad del procesador de consultas de idear un buen plan para ejecutar la consulta.
- Intercalación: cuando se usan funciones que manipulan datos de caracteres, ¿qué intercalación se usará? Algunas funciones usan la intercalación (criterio de ordenación) del valor de entrada; otros usan la intercalación de la base de datos si no se proporciona ninguna intercalación de entrada.
Ejemplos de funciones escalares
En el momento de redactar la documentación de SQL Server, se enumeran más de 200 funciones escalares que abarcan varias categorías, entre las que se incluyen las siguientes:
- Funciones de configuración
- Funciones de conversión
- Funciones de cursores
- Funciones de fecha y hora
- Funciones matemáticas
- Funciones de metadatos
- Funciones de seguridad
- Funciones de cadena
- Funciones del sistema
- Funciones estadísticas del sistema
- Funciones de texto y de imagen
No hay tiempo suficiente en este curso para describir cada función, pero en los ejemplos siguientes se muestran algunas funciones que se usan con frecuencia.
En el ejemplo hipotético siguiente se usan varias funciones de fecha y hora:
SELECT SalesOrderID,
OrderDate,
YEAR(OrderDate) AS OrderYear,
DATENAME(mm, OrderDate) AS OrderMonth,
DAY(OrderDate) AS OrderDay,
DATENAME(dw, OrderDate) AS OrderWeekDay,
DATEDIFF(yy,OrderDate, GETDATE()) AS YearsSinceOrder
FROM Sales.SalesOrderHeader;
A continuación se muestran resultados parciales:
SalesOrderID
OrderDate
OrderYear
OrderMonth
OrderDay
OrderWeekDay
YearsSinceOrder
71774
2008-06-01T00:00:00
2008
junio
1
Domingo
13
...
...
...
...
...
...
...
En el ejemplo siguiente se incluyen algunas funciones matemáticas:
SELECT TaxAmt,
ROUND(TaxAmt, 0) AS Rounded,
FLOOR(TaxAmt) AS Floor,
CEILING(TaxAmt) AS Ceiling,
SQUARE(TaxAmt) AS Squared,
SQRT(TaxAmt) AS Root,
LOG(TaxAmt) AS Log,
TaxAmt * RAND() AS Randomized
FROM Sales.SalesOrderHeader;
Resultados parciales:
TaxAmt
Redondeo
Suelo
Techo
Cuadrado
Raíz
Registro
Aleatorio
70,4279
70,0000
70,0000
71,0000
4960,089098
8,392133221
4,254589491
28,64120429
...
..
...
...
...
...
...
...
En el ejemplo siguiente se usan algunas funciones de cadena:
SELECT CompanyName,
UPPER(CompanyName) AS UpperCase,
LOWER(CompanyName) AS LowerCase,
LEN(CompanyName) AS Length,
REVERSE(CompanyName) AS Reversed,
CHARINDEX(' ', CompanyName) AS FirstSpace,
LEFT(CompanyName, CHARINDEX(' ', CompanyName)) AS FirstWord,
SUBSTRING(CompanyName, CHARINDEX(' ', CompanyName) + 1, LEN(CompanyName)) AS RestOfName
FROM Sales.Customer;
Resultados parciales:
CompanyName
UpperCase
LowerCase
Longitud
Invertido
FirstSpace
FirstWord
RestOfName
Una tienda de bicicletas
UNA TIENDA DE BICICLETAS
una tienda de bicicletas
12
erotS ekiB A
2
A
Tienda de bicicletas
Deportes progresivos
DEPORTES PROGRESIVOS
deportes progresivos
18
stropS evissergorP
12
Progresivo
Deportes
Componentes avanzados de bicicleta
COMPONENTES AVANZADOS DE BICICLETA
componentes avanzados de bicicleta
24
stnenopmoC ekiB decnavdA
9
Avanzado
Componentes de bicicleta
...
...
...
...
...
...
...
...
Funciones lógicas
Otra categoría de funciones permite determinar cuál de varios valores se va a devolver. Las funciones lógicas evalúan una expresión de entrada y devuelven un valor adecuado en función del resultado.
IIF
La función IIF evalúa una expresión de entrada booleana y devuelve un valor especificado si la expresión se evalúa como True, y un valor alternativo si la expresión se evalúa como False.
Por ejemplo, observe la siguiente consulta, que evalúa el tipo de dirección de un cliente. Si el valor es "Oficina principal", la expresión devuelve "Facturación". Para todos los demás valores de tipo de dirección, la expresión devuelve "Correo".
SELECT AddressType,
IIF(AddressType = 'Main Office', 'Billing', 'Mailing') AS UseAddressFor
FROM Sales.CustomerAddress;
Los resultados parciales de esta consulta podrían tener este aspecto:
AddressType
UseAddressFor
Oficina principal
Facturación
Envío
Correo
...
...
CHOOSE
La función CHOOSE evalúa una expresión de entero y devuelve el valor correspondiente de una lista en función de su posición ordinal (basada en 1).
SELECT SalesOrderID, Status,
CHOOSE(Status, 'Ordered', 'Shipped', 'Delivered') AS OrderStatus
FROM Sales.SalesOrderHeader;
Los resultados de esta consulta podrían tener un aspecto parecido al siguiente:
SalesOrderID
Estado
OrderStatus
1234
3
Entregado
1235
2
Enviado
1236
2
Enviado
1237
1
Realizado
...
...
...