Siguiendo con nuestra serie sobre fundamentos del lenguaje de consultas estándar en bases de datos (SQL), y tras ver las funciones de agregación, vamos a ver las funciones escalares del lenguaje de consultas.
Al igual que en cualquier otro lenguaje de programación, SQL dispone de una serie de funciones que nos facilitan la obtención de los resultados deseados. Éstas se pueden utilizar en las cláusulas SELECT, WHERE y ORDER BY que ya hemos estudiado. Otra característica a tener en cuenta es que se pueden anidar, es decir, una función puede llamar a otra función.
En el lenguaje SQL estándar existen básicamente 5 tipos de funciones: aritméticas, de cadenas de caracteres, de fechas, de conversión, y otras funciones diversas que no se pueden incluir en ninguno de los grupos anteriores.
Es muy importante tener en cuenta que, habitualmente, cualquier sistema gestor de bases de datos relacionales (SGBDR) intenta incluir la mayoría de las funciones correspondientes al estándar ANSI SQL, y que además suelen incluir un conjunto adicional de funciones propias. Incluso de una versión a la siguiente dentro de un mismo producto, es común que aparezcan nuevas funciones.
En ese artículo vamos a explicar algunas funciones comunes. Dejamos que investigues y estudies las funciones propias del producto que te interese: SQL Server, Oracle, MySQL...
Aquí nos vamos a limitar a citar algunas de las más significativas, incluyendo una breve descripción de las mismas.
Funciones aritméticas
- ABS(n): Devuelve el valor absoluto de “n”.
- ROUND(m, n): Redondea el número “m” con el número de decimales indicado en “n”, si no se indica “n” asume cero decimales.
- SQRT(n): Devuelve la raíz cuadrada del parámetro que se le pase.
- POWER(m, n): Devuelve la potencia de “m” elevada el exponente “n”.
Funciones de cadenas
- LOWER(c): Devuelve la cadena “c” con todas las letras convertidas a minúsculas.
- UPPER(c): Devuelve la cadena “c” con todas las letras convertidas a mayúsculas.
- LTRIM(c): Elimina los espacios por la izquierda de la cadena “c”.
- RTRIM(c): Elimina los espacios por la derecha de la cadena “c”.
- REPLACE(c, b, s): Sustituye en la cadena “c” el valor buscado “b” por el valor indicado en “s”.
- REPLICATE(c, n): Devuelve el valor de la cadena “c” el número de veces “n” indicado.
- LEFT(c, n): Devuelve “n” caracteres por la izquierda de la cadena “c”.
- RIGHT(c, n): Devuelve “n” caracteres por la derecha de la cadena “c”.
- SUBSTRING(c, m, n): Devuelve una sub-cadena obtenida de la cadena “c”, a partir de la posición “m” y tomando “n” caracteres.
- SOUNDEX(c): Devuelve una cadena con la representación fonética para indexación en inglés (algoritmo Soundex) de la cadena “c”.
Funciones de manejo de fechas
- YEAR(d): Devuelve el año correspondiente de la fecha “d”.
- MONTH(d): Devuelve el mes de la fecha “d”.
- DAY(d): Devuelve el día del mes de la fecha “d”.
- DATEADD(f, n, d): Devuelve una fecha “n” periodos (días, meses años, según lo indicado) superior a la fecha “d”. Si se le pasa un número “n” negativo, devuelve una fecha “n” periodos inferior. De gran utilidad en consultas.
Funciones de conversión
Esta funciones suelen ser específicas de cada gestor de datos, ya que cada SGBDR utiliza nombres diferentes para los distintos tipos de datos (aunque existen similitudes se dan muchas diferencias).
Las funciones de conversión nos permiten cambiar valores de un tipo de datos a otro. Por ejemplo si tenemos una cadena y sabemos que contiene una fecha, podemos convertirla al tipo de datos fecha. Así, por ejemplo:
Algunos ejemplos simples
Una vez vistas estas funciones básicas, vamos a poner ejemplos sencillos de la sintaxis de alguna de ellas:
SELECT ABS(100), ABS(-100), ROUND(10.12345, 2), SQRT(9)
SELECT UPPER(LTRIM(RTRIM(' PEPE '))), REPLICATE('AB', 20), SUBSTRING('ABCDEFGHI', 3, 5)
SELECT MONTH(CAST('20060715' AS DATETIME)), DATEADD(dd, 10, GETDATE())
La function GETDATE() es específica de SQL Server, y nos devuelve la fecha y hora actuales del sistema.
IMPORTANTE: En función del SGBDR que estemos utilizando podremos utilizar los ejemplos de diferente forma. Por ejemplo SQL Server y MySQL permiten utilizarlas y mostrar su resultado incluyendo una cláusula SELECT sin FROM ni ninguna otra cláusula, como en las líneas anteriores. Oracle sin embargo no permite esto, pero tiene una tabla especial llamada DUAL que sólo tiene una fila y una columna y está pensada para estos casos. En el caso de Oracle pues, podemos poner el código que aparece en los ejemplos anteriores y añadirle siempre “ FROM Dual”.
A continuación vamos a poner algunos ejemplos de uso de funciones basados ya en las tablas de la base de ejemplo Northwind para SQL Server:
1) Obtener el número de pedido, la fecha y la fecha de pago (suponiendo que esta última es 30 días después de la fecha de venta), para las ventas realizadas en los últimos 15 días:
SELECT OrderID, OrderDate, DATEADD(dd, 30, OrderDate) FechaPago
FROM Orders
WHERE OrderDate BETWEEN DATEADD(dd, -15, GETDATE()) AND GETDATE()
ORDER BY OrderDate DESC
2) Obtener el importe de cada una de las ventas de la base de datos, redondeado a dos decimales:
SELECT OrderID, ROUND(UnitPrice * Quantity, 2) as Importe FROM [Order Details]
3) Mostrar para cada empresa cliente, su identificador/clave primaria, las tres primeras letras de su nombre, su nombre y el teléfono:
SELECT CustomerID, LEFT(CompanyName, 3), CompanyName, Phone FROM Customers
Con ayuda de estas funciones, usadas tanto para devolver información como para filtrarla, podemos conseguir una gran flexibilidad en las consultas que realicemos.
Fecha de publicación: