Funciones de una Fila, SQL(Parte 1)

Objetivos: 

•Describir varios tipos de funciones disponibles en SQL.
•Utilizar funciones de carácter, numéricas y de fecha en Sentencias SQL.
•Describir el uso de las funciones de conversión.
•Descripción y uso de la tabla DUAL del esquema SYS.

NOTA: Usamos como ejemplo la base de datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.

Las funciones hacen que el bloque de consulta sea más potente y se utilizan para manipular valores de datos.  A continuación mostramos las funciones de una sola fila de carácter, numéricas y de fecha, así como las que convierten datos de tipo a otro.

Las funciones son una característica muy potente de SQL. Se pueden utilizar para:

 • Realizar cálculos en datos.

 • Modificar elementos de datos individuales.

 • Manipular la salida para grupos de filas.

 • Formatear fechas y números para su visualización.

 • Convertir tipos de datos de columnas.

Las funciones SQL a veces toman argumentos y siempre devuelven un valor.

Nota: En su mayor parte, las funciones que se describen a continuación son específicas de la versión Oracle de SQL.

_____________________________________________________________________________________
Funciones de una Sola Fila

• Estas funciones operan sólo en filas únicas, actúan en cada fila de la consulta por lo tanto devuelven un resultado por fila.

• Pueden modificar el tipo de dato.

• Se pueden utilizar en cláusulas SELECT, WHERE y ORDER BY y Se pueden anidar(una dentro de otra).

 • Aceptan uno o mas argumentos que pueden ser columnas o expresiones. 

Hay diferentes tipos de funciones de una sola fila, a continuación explicamos las siguientes:

 • De carácter
 • Numéricas
 • De fecha
 • De conversión
 • Generales

SINTAXIS:

function_name [(arg1,arg2,...)]

En la sintaxis:

function_name es el nombre de la función.

arg1, arg2  es cualquier argumento que vaya a utilizar la función. Se  puede representar con un nombre de columna o una expresión.

_____________________________________________________________________________________
Funciones de Carácter.

• Aceptan la entrada de caracteres y pueden devolver valores de carácter y numéricos.

• Las funciones de carácter se pueden dividir en:

  - Funciones de manipulación de mayúsculas/minúsculas.

LOWER, UPPER e INITCAP son las tres funciones de conversión de mayúsculas/minúsculas.

•LOWER(column|expression): Convierte cadenas de caracteres con mayúsculas/minúsculas mezcladas o mayúsculas a minúsculas.

•UPPER (column|expression): Convierte cadenas de caracteres con mayúsculas/minúsculas mezcladas o minúsculas a mayúsculas.

•INITCAP (column|expression): Convierte la primera letra de cada palabra a mayúsculas y el resto de las letras a minúsculas.

Ejemplos:

SELECT LOWER('EL CÓDIGO DE PUESTO DEL EMPLEADO ')||UPPER(last_name)||' es '
                 ||INITCAP(job_id) AS "EMPLOYEE DETAILS"
FROM employees;

/*En el query anterior el OUTPUT luce de la siguiente manera: 'EL CÓDIGO DE PUESTO DEL EMPLEADO' aparece en minúscula gracias a la función LOWER, el apellido del empleado(last_name) se imprime en mayúscula debido a la función UPPER y el código de puesto(job_id) tendría solo la primera letra Mayúscula; sugerencia: ejecutar el mismo Query pero sin las funciones*/

-----
SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(last_name) = 'higgins';

/*En el Query anterior se usa la función LOWER en la Cláusula WHERE, se convierte el campo last_name a minúscula para así comparar con la cadena de caracteres 'higgins', esta técnica de búsqueda es util si no se conoce el formato original de la data en la Base de Datos*/
---

Funciones de manipulación de caracteres.

Nota: Las funciones analizadas en esta lección únicamente suponen una parte de

las disponibles.

CONCAT(column1|expression1, column2|expression2: Concatena el primer valor de carácter con el segundo; equivalente al operador de concatenación (||) pero la función CONCAT solo concatena dos cadenas de caracteres.

SUBSTR(column|expression,m[,n]): Devuelve los caracteres especificados del valor de carácter empezando por la posición de carácter m, con n caracteres de longitud (Si m es negativo, el recuento se inicia desde el final del valor de carácter. Si se omite n, se devuelven todos los caracteres hasta el final de la cadena).

LENGTH(column|expression): Devuelve el número de caracteres de la expresión.

INSTR(column|expression, ’string’, [,m], [n]): Devuelve la posición numérica de una cadena especificada. Opcionalmente, puede proporcionar una posición m para iniciar la búsqueda y la incidencia n de la cadena. m y n tienen por defecto el valor 1, lo que significa iniciar la búsqueda al principio de la búsqueda e informar de la primera incidencia.

LPAD(column|expression, n, 'string'): Agrega n cantidad de caracteres('string') a la izquierda(LEFT) de column|expression.

RPAD(column|expression, n, 'string'): Lo mismo que LPAD, pero RPAD lo rellena a la derecha(Right).

REPLACE(text, search_string, replacement_string): Busca en una expresión de texto(text) una cadena de caracteres(search_string) y, de encontrarla, la sustituye por una cadena de sustitución especificada(replacement_string).

TRIM(leading|trailing|both, trim_character FROM trim_source): Le permite recortar caracteres de cabecera o finales (o ambos) de una cadena de caracteres. Si trim_character o trim_source son literales de carácter, los debe poner entre comillas simples, esta función está disponible en Oracle8i y versiones posteriores.

Ejemplos:

Función	Resultado
CONCAT('Hello', 'World')	HelloWorld
SUBSTR('HelloWorld',1,5)	Hello
LENGTH('HelloWorld')	10
INSTR('HelloWorld', 'W')	6
LPAD(salary,10,'*')	*****24000
RPAD(salary, 10, '*')	24000*****
REPLACE ('JACK and JUE','J','BL')	BLACK and BLUE
TRIM('H' FROM 'HelloWorld')	elloWorld

SELECT 

 CONCAT('Primera_cadena*','Segunda_cadena') AS funcion_CONCAT,

 SUBSTR('Retorna ESTO!',9,5) AS funcion_SUBSTR,

 LENGTH(email) AS funcion_LENGTH,

 INSTR('Posición letra K!!!','K') AS funcion_INSTR,

 LPAD(first_name,10,'$') AS funcion_LPAD,

 RPAD(salary,10,'@') AS funcion_RPAD,

 REPLACE('Conito Arnito','nito','mando') AS funcion_REPLACE,

 TRIM(LEADING '0' FROM '0012300') AS funcion_TRIM1,

  TRIM(TRAILING 0 FROM '0012300') AS funcion_TRIM2,

  TRIM(BOTH 0 FROM 0012300) AS funcion_TRIM3

FROM employees;

/*En el Query anterior concateno('Primera_cadena*' con 'Segunda_cadena'), retorno 5 caracteres de ('Retorna ESTO!') a partir de la posición 9, determino el tamaño del campo email de la tabla employees para cada empleado, determino la posición de la letra 'K' en la cadena 'Posición letra K!!!', relleno por la izquierda el campo first_name de la tabla employees con '$' hasta la posición 10, relleno por la derecha el campo salary de la tabla employees con '@' hasta la posición 10, reemplazo la cadena 'nito'en 'Conito Arnito' con 'mando' y por último remuevo los 0 de la cadena/numero 0012300 primero por el frente luego por detrás y por último por ambos lados*/

_____________________________________________________________________________________

Funciones Numéricas.

Las funciones numéricas aceptan la entrada de números y devuelven valores numéricos. Esta sección describe algunas de las funciones numéricas.

ROUND(column|expression, n): Redondea la columna, la expresión o el valor a n posiciones decimales o, si se omite n, a ninguna (Si n es negativo, se redondean los números a la izquierda del separador decimal).Si el segundo argumento es 2, el valor se redondea a dos posiciones decimales. A la inversa, si el segundo argumento es –2, el valor se redondea a dos posiciones enteras a la izquierda (redondeando a la unidad más cercana a 10).

TRUNC(column|expression, n): Trunca la columna, la expresión o el valor a n posiciones decimales o, si se omite n, opta por el valor por defecto de cero. Sus parámetros funcionan igual que la función ROUND pero truncando los números.

MOD(m,n): Devuelve el resto de m dividido por n(División modular), osea busca el resto del primer argumento dividido por el segundo.

Tabla DUAL

La tabla DUAL es propiedad del usuario SYS y pueden acceder a ella todos los usuarios. Contiene una columna, DUMMY, y una fila con el valor X. La tabla DUAL resulta útil si desea devolver un valor sólo una vez (por ejemplo, el valor de una constante, una pseudocolumna o una expresión que no se deriva de una tabla con datos de usuario). La tabla DUAL se utiliza generalmente para la integridad de sintaxis de la cláusula SELECT, ya que las sentencias SELECT y FROM son obligatorias y varios cálculos no necesitan seleccionar de tablas reales.

Ejemplos:

SELECT ROUND(45.923,2) AS ROUND1, 

                ROUND(45.923,2) AS ROUND2, 

                ROUND(45.923,-1) AS ROUND3
FROM DUAL;

/*En el Query anterior ROUND redondea 45.923 a dos posiciones decimales resultando: 45.92, luego el mismo valor es redondeado a 0 posiciones decimales(unidad) resultando: 46 y por último es redondeado a la decena(-1) resultando 50*/

-----

SELECT TRUNC(45.923,2) AS TRUNC1, 

                TRUNC(45.923,0) AS TRUNC2, 

                TRUNC(45.923,-1) AS TRUNC3
FROM DUAL;

/*En el Query anterior TRUNC trunca 45.923 a dos posiciones decimales resultando: 45.92, luego el mismo valor es truncado a 0 posiciones decimales(unidad) resultando: 45 y por último es redondeado a la decena(-1) resultando 40*/

-----

SELECT last_name, salary, MOD(salary, 5000)
FROM employees
WHERE job_id = 'SA_REP';

/*En el Query anterior se muestra el apellido, salario y la división modular entre el salario y 5000 de cada Empleado con job_id = 'SA_REP'.*/

_____________________________________________________________________________________

Debido a lo extenso que es el tema de las Funciones de una Fila, decidí dividirlo para así no abarrotar un solo post.

======>>Manejo de las Fechas y sus Funciones.

Fuente: Base de Datos Oracle 10g: Conceptos Fundamentales de SQL 1.