regexp_like en oracle

7/25/2019 Regexp_like en Oracle

1/11

REGEXP_LIKE

Introduccin al uso de las Expresiones Regulares en una Base de Datos Oracle

Por Fernando Garca

Publicado en marzo 2012

Prlogo

Histricamente Oracle haba ofrecido prestaciones muy bsicas para el uso de expresiones regulares y la

manipulacin de cadenas de caracteres !"uellos desarrolladores "ue necesitaban hacer uso de expresiones

regulares se #ean prcticamente obligados a extraer los datos de la base para manipular las cadenas de

caracteres en capas intermedias de la aplicacin ! partir de la #ersin 10g$ con la introduccin de las

llamadas funciones %&'&(P )%&'ular &(Pressions*$ Oracle nos ofrece una batera de sofisticadas y

potentes herramientas nati#as "ue permiten al desarrollador centralizar el procesamiento intensi#o de

cadenas de caracteres dentro de la base de datos Oracle y con los lengua+es ,-. y P./,-.

Tabla de ontenidos

1*-u son las &xpresiones %egulares

2*Para "u sir#en las &xpresiones %egulares3*&ntendiendo las &xpresiones %egulares

31*&l metacaracter Punto )*

32*&l metacaracter ,uma )4*

33*&l metacaracter !sterisco )5*

36*&l metacaracter 7char89

3:*&l metacaracter 7;char89

3*&l metacaracter de ancla+e de fin de lnea )?*

3@*&l metacaracter de escape )A*

6*Bonstruyendo &xpresiones %egulares comple+as

:*Cso de las &xpresiones %egulares en Oracle


2/11

[n|p]ata

.os metacaracteres son los corchetes y el smbolo pipe )J* &l resto son literales

.os corc%etesagrupan a #arios caracteres en un solo caracter

&l smbolo pipeindica un smbolo u otroF es decir la KnK o la KpK

.uego$ la expresin regularE

[n|p]ata

Boincide con las cadenasE

natapata

,eguramente el lector se preguntar cmo determinar "u elementos de una expresin regular son literales y

"u elementos son metacaracteres Ls adelante encontrar la respuesta ,igamos

!Para &u# sir'en las Expresiones Regulares$

&n una base de datos Oracle existen di#ersos escenarios en "ue la implementacin de expresiones regulares

constituye una herramienta de gran utilidadE

B(s&ueda de texto).as expresiones regulares nos permiten hacer bDs"uedas comple+as de

cadenas de caracteres dentro de las columnas de nuestras tablas

*or+ateo de datos)Podemos modificar los datos proyectados en las sentencias ,&.&BI$

in#irtiendo palabras$ agregando o "uitando caracteres$ etc

De,inicin de constraints)! fin de forzar el contenido de una columna para "ue se a+uste a un

formato determinadoE casilla de correo$ nDmero telefnico$ etc

-anipulacin de datos)Por e+emplo$ regularizando datos en procesos de migracin desde

aplicaciones legacy y aplicando reglas de bDs"ueda y reemplazo ),earch M %eplace*

Entendiendo las Expresiones Regulares

Bomo se haba dicho anteriormente$ en una expresin regular encontramos literales y metacaracteres !l

analizar una expresin regular$ lo primero "ue tenemos "ue hacer es determinar "u elementos son literales y

"u elementos son metacaracteres )tambin llamados operadores* &xiste una larga lista de metacaracteres

soportados por Oracle o es la intencin estudiarlos a todos en este documento ! continuacin iremos

#iendo algunos

El +etacaracter punto .)/

&l metacaracter punto coincide con cual"uier carcter Por e+emplo$ en la expresin regular

ca.a

Para "ue una cadena de caracteres coincida con dicho patrnE

N ebe estar el literal KcK

N .e debe seguir el literal KaK

N .e debe seguir un y solamente un carcter cual"uiera

N .e debe seguir el literal KaK

&n la expresin regular encontramos 3 literales y el metacaracter punto !nalicemos las siguientes cadenas y

#eamos cuales coinciden con el patrn buscado y cules no


3/11

Badena Boincide !nlisis

casa ,i Boinciden los literales y en la posicin del operador punto aparece slo un carcter

cana ,i Boinciden los literales y en la posicin del operador punto aparece slo un carcter

cara ,i Boinciden los literales y en la posicin del operador punto aparece slo un carcter

canta o &n la posicin del punto aparece ms de un carcter

pala o &l literal KpK en la primer posicin no respeta el literal c definido en la expresin regular

El +etacaracter su+a .0/

&l metacaracter suma coincide con una o ms ocurrencias de la subexpresin "ue lo precede Por e+emplo$

analicemos la expresin regular

cas+a


N ebe estar el carcter KcK

N .e debe seguir el carcter KaK

N .e debe seguir una o mas ocurrencias del carcter KsK


Qemos nue#amente tres literales y el metacaracter ,uma Rus"uemos el patrn en las siguientes cadenas y

#eamos las coincidencias


cassacassssacassssssssa

,i &l smbolo ms )4* es precedido por la subexpresin SsS Por lo tanto todas lascadenas "ue tienen una o ms SsS en a partir de la tercer posicin y respetan elresto de los literales$ coinciden con el patrn representado en la expresin regular

Baa o &l patrn definido re"uiere al menos una TsU en la tercer posicin

.asa o o respeta todos los literales de la expresin regular

Iassa o o respeta todos los literales de la expresin regular

El +etacaracter asterisco .1/

Boincide con cero o ms ocurrencias de la subexpresin "ue le precede al asterisco !nalicemos la expresinE

cas*a


N ebe estar el carcter KcK N .e debe seguir el carcter KaK N .e debe seguir cero o mas ocurrencias del

carcter KsK &s decir "ue el carcter KsK puede o no estar N .e debe seguir el carcter KaK

!nalicemos "u pasa con las mismas cadenas de caracteres del e+emplo anteriorE


4/11


Basacassssacassssssssa

,i &l smbolo asterisco )5* es precedido por la subexpresin SsS Por lo tanto todas lascadenas "ue tienen cero o ms SsS a partir de la tercera posicin y respetan el restode los literales$ coinciden con el patrn representado en la expresin regular

caa ,i &l patrn acepta la no existencia del carcter KsK en la tercera posicin

lasa o o respeta todos los literales de la expresin regular

tassa o o respeta todos los literales de la expresin regular

El +etacaracter 2c%ar)))3

Boincide con una sola ocurrencia de alguno de los caracteres de la lista entre corchetes &n la lista$ todos los

caracteres son tomados como literales excepto algunos caracteres "ue son interpretados como

metacaracteres !"u nos limitaremos a mencionar el metacaracter KNK para representar rangos de caracteres

!nalicemos la siguiente expresin regularE

ca[snt]a




N .e debe seguir uno y solamente uno de los siguientes caracteresE KsK$ KnK o KtK



casa ,i Boinciden los literales y en la posicin del operador aparece slo un carcter y es de losde la lista

cana ,i Boinciden los literales y en la posicin del operador aparece slo un carcter y es de losde la lista

cata ,i Boinciden los literales y en la posicin del operador aparece slo un carcter y es de losde la lista

cara o &n la posicin del operador aparece un solo carcter pero no est en la lista

canta o ,i bien en la posicin del operador aparecen literales de la listaF son ms de uno

pasa o o respeta el primer literal de la lista

Bomo se di+o anteriormente$ en la lista se puede utilizar el metacaracter rango KNK Haciendo uso de este

metacaracter la siguiente lista de caracteresE

[1234568!]

Podemos expresarla como


5/11

[1-!]

V la siguiente lista de caracteres

[abc"e#]

Podemos expresarla como

[a-#]

!nalicemos ahora la siguiente expresin regularE

a[3-][#-]!

Para "ue una cadena coincida con el patrnE

N ebe tener el literal KaK

N .e debe seguir un numero de 3 a = &s decirE 3$ 6$ :$ < =

N .e debe seguir una letra de la f a la i &s decir KfK$ KgK$ KhK o KiK

N .e debe seguir el numero @


a3h@ ,i Boinciden los literales y en la posicin de los operadores aparecen literales incluidos enlos rangos especificados en el patrn de la expresin regular

a33f@ o &n la posicin del primer operador aparece ms de un caracter

a3z@ o .a KzK no est incluida en el rango del segundo operador

El +etacaracter 24c%ar)))3

&l sombrerito );* "ue precede a la lista indica "ue los literales "ue le siguen no deben estar en la cadena de

caracteres para "ue se produzca una coincidencia Qeamos$

ca[^snt]a




N .e debe seguir uno y solamente un carcter "ue no sea ni KsK ni KnK ni KtK



casa o Boinciden los literales y en la posicin del operador aparece un carcter de los negadosen la lista

cana o Boinciden los literales y en la posicin del operador aparece un carcter de los negadosen la lista

cata o Boinciden los literales y en la posicin del operador aparece un carcter de los negadosen la lista


6/11

cara ,i Boinciden los literales y en la poscion del operador aparece un carcter "ue no est enla lista de literales negados

canta o &n la posicin del operador aparece ms de un #alor

para o o respeta el primer literal de la lista

El +etacaracter subexpresin .expr/

Bonsidera a toda la expresin entre parntesis como una unidad .a expresin puede ser una simple cadena

de literales o una expresin comple+a conteniendo otros metacaracteres !nalicemos la siguiente expresin

regular

cha%(h&'a)*cha%

!hora el asterisco precede a la expresin

h&'a

&l asterisco indica "ue la expresin

h&'a

Puede aparecer cero ms #eces


chauchau ,i Boinciden los literales y la expresin KholaK aparece cero #eces

chauholachau ,i Boinciden los literales y la expresin KholaK aparece una #ez$ es decir cero oms #eces

chauholaholachau ,i Boinciden los literales y la expresin KholaK aparece dos #eces$ es decir cero oms #eces

holachau o o aparecen el literal chauK "ue precede al TholaU

chau o o aparece el literal KchauK "ue precede al TholaU

El +etacaracter de ancla5e de principio de l6nea .4/

Boincide con el principio de lnea y el operador est representado con el caracter sombrerito );* &n la

expresin regular

^h&'a

.os literales "ue componen la palabra KholaK deben estar al inicio de la lnea para "ue se produzca la

coincidencia con el patrn expresado



7/11

hola ,i .os literales "ue componen la palabra KholaK estn al inicio de la lnea

chauhola o .a lnea no comienza con los literales "ue conforman la palabra KholaK

holachau ,i .os literales "ue componen la palabra KholaK estn al inicio de la lnea

holahola ,i .os literales "ue componen la palabra KholaK estn al inicio de la lnea

El +etacaracter de ancla5e de ,in de l6nea .7/

Boincide con el final de lnea y el operador est representado con el carcter pesos )?* &n la expresin

regular

h&'a$

.os literales "ue componen la palabra KholaK deben estar al final de la lnea para "ue se produzca la

coincidencia con el patrn expresado


Hola ,i .os literales "ue componen la palabra KholaK estn al final de la lnea

chauhola ,i .a lnea finaliza con los literales "ue conforman la palabra KholaK

holachau o .os literales "ue componen la palabra KholaK no estn al final de la lnea

holahola ,i .os literales "ue componen la palabra KholaK estn al f inal de la lnea

El +etacaracter de escape .8/Precediendo a un metacaracter con el smbolo de escape$ el metacaracter ser interpretado como un literal

&l doble carcter de escape )AA* permite considerar al carcter de escape como literal

&n la expresin regular

h&'a\*


hola o Walta el literal K5K "ue sigue literal KaK

Hola5 ,i Boinciden todos los literales

hol5 o Waltan los literales KaK y T5U

onstru9endo Expresiones Regulares co+ple5as

Hasta a"u hemos #isto algunos de los metacaracteres "ue se usan con ms frecuencia Bombinando #arios

de estos metacaracteres en una sola expresin regular$ podemos hacer construcciones ms comple+as y muy

poderosas !nalicemos la siguiente expresin regular en la "ue encontramos #arios metacaracteres


8/11

^h&'a[0-!]*cha%[^a]+$

Hagamos un desglose de la expresin regularE

;hola !l principio de la lnea debe estar la palabra KholaK

70N@95 .uego de la palabra KholaK deben aparecer cero o mas dgitos del 0 al @

chau .uego debe aparecer el literal KchauK

7;a94? .a lnea debe continuar hasta el final con uno o ms caracteres distintos de KaK

Rus"uemos el patrn en algunas cadenas de caracteres


hola123chaub ,i Bomienza con el texto KholaK .e siguen #arios dgitos entre 0 y @ .e sigue elliteral KchauK y finaliza con al menos una letra distinta de KaK

holachauc ,i Bomienza con el texto KholaK o aparecen dgitos del 0 al @ )el asterisco indica"ue esto es aceptado para "ue haya coincidencia* .uego aparece el literal KchauKy se finaliza con una letra distinta de KaK

hola0chaua o Winaliza con una TaU

:so de las Expresiones Regulares en Oracle

Hasta a"u hemos #isto un poco de teora y algunos e+emplos prcticos para conocer y aprender un poco

acerca de las expresiones regulares Pero$ cmo podemos hacer uso de este conocimiento ad"uirido en una

base de datos Oracle

! partir de la #ersin 10g Oracle nos ofrece un grupo de nue#as funciones y condiciones para poder mane+ar

expresiones regulares en el lengua+e ,-.E

%&'&(PX.YZ& Bondicin "ue se puede utilizar en la clusula [H&%& de una sentencia ,-. ,&.&BI y"ue permite retornar a"uellas filas "ue coinciden con el patrn especificado en unaexpresin regular

%&'&(PXBOCI Wuncin "ue permite contar el nDmero de #eces "ue un patrn aparece en una cadenade caracteres

%&'&(PXY,I% Wuncin "ue permite determinar la posicin de inicio de un patrn especfico en unacadena de caracteres

%&'&(PX%&P.!B& Wuncin "ue permite hacer bDs"ueda y reemplazo en una cadena de caracteresutilizando expresiones regulares para la bDs"ueda

%&'&(PX,CR,I% Wuncin para extraer de una cadena una subcadena de caracteres "ue coincidan con unpatrn especificado en una expresin regular


9/11

Qeamos algunos e+emplos de implementacin de estas condiciones y funciones combinadas con el uso de

expresiones regulares

.a condicin %&'&(PX.YZ& es similar a la condicin .YZ& pero$ a diferencia del .YZ&$ utiliza patrones

basados en expresiones regulares para la bDs"ueda de coincidencias en las cadenas de caracteres

analizadas

&n el siguiente e+emplo$ #emos una tabla de empleados cuyos nombres fueron migrados de un sistema

legacy &n muchos casos el nombre ha "uedado separado del apellido por #arios espacios en blancoE

se'ect n&be #& ep'ea"&s/----------------------------------------enan"& 7acaace'& %&s ace'& ch&a7ea"& 9e::a;'asa aan #a'&"&'#& hee ee?p@'e(n&beB C[ ][ ][ ]*C)/----------------------------------------enan"& 7acaace'& %&s

ace'& ch&a7ea"& 9e::a&"&'#&


10/11

6 &>s se'ecte".

Rastante bien ,al#o el caso de Larcelo Ochoa$ en donde nos "ued un espacio en blanco al principio de la

lnea &liminemos entonces los espacios en blanco "ue estn al principio de la lnea

%p"ate ep'ea"&s 2 set n&be D ee?p@ep'ace(n&beB C^ C)

3 >hee ee?p@c&%nt(n&beB C^ C) 0 4 E1 &> %p"ate". se'ect n&be #& ep'ea"&s/----------------------------------------enan"& 7acaace'& %&sace'& ch&a7ea"& 9e::a;'asa aan #a'&"&'#&


11/11

E9ab'e a'tee".

Qeamos "u ocurre al ingresar nue#as filas a la table de impuestos

nset nt& p%est&s (c%t) va'%es (C20-1!!866-2C)/

1 &> ceate". nset nt& p%est&s (c%t) va'%es (C1?-1!!866-2C)/nset nt& p%est&s (c%t) va'%es (C1?-1!!866-2C)* at 'ne 1HA-022!0H chec c&nstant (I.;@;FG9) v&'ate"

nset nt& p%est&s va'%es (C12888122812C)/nset nt& p%est&s va'%es (C12888122812C)* at 'ne 1HA-022!0H chec c&nstant (I.;@;FG9) v&'ate"

regexp_like en oracle

Documents