domingo, 22 de septiembre de 2013

Regular Expressions (expresiones regulares) en PostgreSQL

Cuando las búsquedas con el operador Like no son lo suficientemente flexibles para localizar los registros que necesitamos, PostgreSQL permite las búsquedas de registos mediante el uso de regular expressions (expresiones regulares).

En este par de posts: Regular Expressions en PL/SQL Oracle, parte I y parte II coloque unos ejemplos de la utilización de expresiones regulares en Oracle.

Ahora utilizaré los mismos ejercicios de esas entradas, pero ahora para mostrar las regular expressions en PostgreSQL.

Creamos una tabla llamada membership:

Ahora insertamos algunos registros:

El operador ~

Como primer ejemplo utilizamos el operador ~ (LIKE versión regular expression) en la consulta para buscar en la columna MEMBERSHIP_LASTNAME aquellos registros que empiecen con la letra D.

Aquí un segundo ejemplo en donde buscamos en la columna MEMBERSHIP_LASTNAME aquellos registros que comiencen con las letras de la A a la F.

El operador inverso !

Ahora mostraré los ejemplos anteriores utilizando el operador inverso ! o la negación del operador ~ para el primer ejemplo, esta consulta busca en la columna MEMBERSHIP_LASTNAME aquellos registros que no empiecen con la letra D.

El mismo operador de negación para el segundo ejemplo, esta consulta busca en la columna MEMBERSHIP_LASTNAME aquellos registros que no empiecen con las letras de la A a la F.

El operador case-insensitive *

Tanto ~ como ! son case-sensitive, si necesitas que ambos operadores sean case-insensitive se combinan con el operador * , como en el ejemplo siguiente donde buscamos en la columna MEMBERSHIP_LASTNAME aquellos registros que después del primer caracter tenga la letra O mayúscula repetida una o más veces.

Esta búsqueda no dio resultados porque excepto la primera letra de los registros en la columna MEMBERSHIP_LASTNAME el resto se encuentran en letras minúsculas, entonces para que la consulta encuentre resultados utilizamos el operador *.

Ahora si la consulta anterior mostro los resultados.

Descargar el código SQL(Download the source code)

viernes, 20 de septiembre de 2013

Regular Expressions en PL/SQL Oracle, parte II

En este post anterior mostré ejemplos de búsqueda con las funciones regexp_like y regexp_instr.

A continuación mostrare ejemplos de las funciones regexp_substr y regexp_replace respectivamente.

Como primer ejemplo de la función regexp_substr, obtendré dos subcadenas (substring), una de la columna MEMBERSHIP_NAME donde el carácter a se repita dos veces en cada registro y otra de la columna MEMBERSHIP_DUE en donde la cantidad comience con un dígito del 1 al 6 y después uno o más dígitos que se repitan.



Como segundo ejemplo de la función regexp_substr obtendré un substring de la columna MEMBERSHIP_DATE cuyos registros terminen con los dígitos del 1 al 6 repetidos una o dos veces.



Como primer ejemplo de la función regexp_replace buscaré dentro de la concatenación de las columnas MEMBERSHIP_NAME y MEMBERSHIP_LASTNAME los registros que tengan un carácter o repetido a partir de la posición 4 y lo sustituiré por la cadena (**found**).



Como último ejemplo ejecutamos la función regexp_replace en un texto y reemplazamos la palabra ‘fox’ por la palabra ‘SUPER CAT’ a partir de la posición 1.



Descarga el código PL/SQL

lunes, 16 de septiembre de 2013

Regular Expressions en PL/SQL Oracle, parte I

Una expresión regular (regular expression) es un conjunto de caracteres (signos) conocido como patrón que al buscarse coincide una o más veces en una cantidad considerable de texto, estos patrones se construyen con una notación de caracteres ordinarios y metacaracteres, los cuales tienen un significado especial dentro de la expresión regular e indican las reglas a las que deben someterse los caracteres ordinarios para su interpretación estos bloques básicos de construcción son similares a una expresión algebraica o a un mini lenguaje de programación.

A continuación algunos de los metacaracteres y su significado:

  • ^ coincide el patrón de búsqueda al inicio de una línea.
  • $ coincide el patrón de búsqueda al final de una línea.
  • . coincide cualquier caracter en cualquier lugar.
  • [] especifica un rango de caracteres
  • ? ubica un caracter opcional.
  • + ubica uno o más caracteres.
  • - ubica cero o más caracteres.
  • {n} ubica un caracter que aparece n veces.
  • {n,} ubica un caracter que aparece n o más veces.
  • {n,m}ubica un caracter que aparece de n a m veces.
  • | disyunción o sea un or lógico entre caracteres.

Las expresiones regulares son ampliamente utilizadas en Linux o en otros lenguajes Open Source, además de plataformas como Java y.NET y en bases de datos como PostgreSQL y Oracle. En Oracle las expresiones regulares son utilizadas cada vez que necesites operaciones de búsqueda demasiado complicadas en donde los comandos SELECT y LIKE no sean suficientes. Oracle tiene las siguientes cuatro funciones para su utilización:

  • REGEXP_LIKE: es la versión de expresiones regulares del comando LIKE. Una función booleana que regresa TRUE,FALSE o NULL si en el texto existe una coincidencia con la expresión regular.
  • REGEXP_INSTR: esta función regresa la posición del caracter en el texto donde se encontró una coincidencia con la expresión regular.
  • REGEXP_SUBSTR: extrae una coincidencia de texto encontrada con la expresión regular.
  • REGEXP_REPLACE: ejecuta una operación de búsqueda y reemplazo si se encuentra una coincidencia en el texto.

Como ejemplo de su uso, creamos la siguiente tabla:


Después insertamos los siguientes registros para comenzar a utilizar las funciones.

Elizabeth  Bishop 36736-36738 976.063 02/08/1911
Charles Dickens 36734-5461 2244.789 07/02/1812
Jack London 5462-37314 898.127 12/01/1876
Joseph Conrad 37315-5463 1193.493 03/12/1857
Gustave Flaubert 37313-37316 1435.384 12/12/1821
John Milton 37317-37296 1348.582 09/12/1608
Samuel Taylor 37292-37318 207.449 21/10/1772
Virginia Wolf 37061-106 2077.947 25/01/1882
Walter  Scott 37319-37320 412.72 15/08/1771
Robert Louis  Stevenson 37945-37946 1033.54 13/11/1850
Joseph Rudyard  Kipling 37947-12556 382.41 30/12/1865
Arthur Conan Doyle 12557-10964 1844.945 22/05/1859
George  Orwell 54722-3236 2139.874 25/01/1903

Como primer ejemplo utilizamos la función REGEXP_LIKE para obtener de la columna MEMBERSHIP_LASTNAME los regitros que comienzan con la letra D.La consulta es:



Ahora utilizamos la función REGEXP_LIKE para obtener de la columna MEMBERSHIP_DUE los regitros que terminan con el número 3.La consulta es:



Por último, utilizamos la función REGEXP_LIKE para obtener de la columna MEMBERSHIP_LASTNAME los regitros que tengan las letras de la A a la F. La consulta es:



Ahora ejemplos con la función REGEXP_INSTR. En el primer ejemplo buscamos los registros que en la columna MEMBERSHIP_NAME tengan de 1 a 2 veces la letra A y cuyo posición de coincidencia del texto sea mayor a 0.



En este segundo ejemplo con REGEXP_INSTR buscamos todos los registros que en la columna MEMBERSHIP_NAME comiencen con la letra J o la letra E.



Un último ejemplo con REGEXP_INSTR buscamos todos los registros que en la columna MEMBERSHIP_DATE terminen en el penúltimo dígito del 0 al 9 y en el último dígito del 1 al 2.



Download el código fuente PL/SQL

domingo, 25 de agosto de 2013

Obtener el primer día del mes en curso con una función first_day() con PL/SQL en Oracle.

En PL/SQL existe la función LAST_DAY() con la que se obtiene el último día del mes en curso, entonces si tenemos que cumplir un requerimiento en donde necesitamos un campo o una variable con el último día del mes en curso simplemente ejecutamos esta función:


Ahora bien, si el requerimiento a cumplir se trata de obtener el primer día del mes en curso, no existe en PL/SQL Oracle una función predeterminada, por lo que para llegar a ese resultado basta con restarle un mes a la fecha en curso con la función ADD_MONTHS(), le aplicamos la función LAST_DAY() para obtener el último día del mes anterior y sumarle un día.


Aquí otra forma alterna de llegar al mismo resultado.



Descarga el código PL/SQL

lunes, 19 de agosto de 2013

ADO.NET Helper para Oracle (Update)

Todos los programas que utilizan las clases e interfaces del proveedor predeterminado de ADO.NET para Oracle que se encuentran en el ensamblado System.Data.Oraclient deben actualizarse por las clases e interfaces que se encuentran en el ensamblado Oracle.DataAccess.Client, que es el proveedor nativo de Oracle para ADO.NET, este ensamblado se descarga del siguiente enlace Oracle Data Provider for .NET


A continuación el código actualizado de las clases Helper de ADO.NET que publique en este post.

El código de la clase OracleDataBase


El código de la clase OracleDataBaseCommand

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Oracle.DataAccess.Client;

namespace OracleHelper
{
    internal sealed class OracleDataBaseCommand
    {

internal static int Insert(string commandText, Dictionary parameters, 
System.Data.CommandType cmdtype)
{
            
int resp = 0;
try
{
    using (OracleConnection conn = OracleDataBase.GetInstance().GetConnection())
    {
        using (OracleCommand cmd = new OracleCommand(commandText, conn))
        {
            cmd.CommandType = cmdtype;
            if (parameters != null)
            {
                foreach (KeyValuePair pair in parameters)
                {
                    cmd.Parameters.Add(new OracleParameter(pair.Key, pair.Value));
                }
            }
            resp = cmd.ExecuteNonQuery();
        }
    }
}
catch (OracleException ex)
{
    Logger.LogWriteError(ex.Message);
    throw ex;
}
return resp;
}
internal static int Update(string commandText, Dictionary parameters, 
System.Data.CommandType cmdtype)
{
            
try
{
    return Insert(commandText, parameters, cmdtype);
}
catch (OracleException ex)
{
    Logger.LogWriteError(ex.Message);
    throw ex;
}
}
internal static OracleDataReader GetReader(string commandText, Dictionary parameters,
System.Data.CommandType cmdtype)
{
OracleDataReader reader = null;
try
{
    OracleConnection conn = OracleDataBase.GetInstance().GetConnection();
    using (OracleCommand cmd = new OracleCommand(commandText, conn)) {
        if (parameters != null)
        {
            foreach (KeyValuePair pair in parameters)
            {
                cmd.Parameters.Add(new OracleParameter(pair.Key, pair.Value));
            }
        }
        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }
}
catch (Exception ex) {
    Logger.LogWriteError(ex.Message);
    throw ex;
}
return reader;
}

internal OracleDataReader GetParameterizedReader(string commandText, OracleParameter[] parameters, 
System.Data.CommandType cmdtype)
{
OracleDataReader reader = null;
OracleConnection conn = OracleDataBase.GetInstance().GetConnection();
using (OracleCommand cmd = new OracleCommand(commandText, conn))
{
    cmd.CommandType = cmdtype;
    if (parameters != null)
        cmd.Parameters.AddRange(parameters);
    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
return reader;
}
}
}

Como ejemplo del uso del helper, tenemos el código de la clase CustomerDAC cuya funcionalidad es tener los métodos CRUD para una entidad Customer.


El código de la clase Customer


Para finalizar el código de una aplicación de consola que muestra el uso de la clase CustomerDAC.


Download el código fuente para Xamarin Studio o Visual Studio