regexp_like en oracle
Post on 01-Mar-2018
226 Views
Preview:
TRANSCRIPT
-
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
-
7/25/2019 Regexp_like 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
-
7/25/2019 Regexp_like en Oracle
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
Para "ue una cadena de caracteres coincida con dicho patrnE
N ebe estar el carcter KcK
N .e debe seguir el carcter KaK
N .e debe seguir una o mas ocurrencias del carcter KsK
N .e debe seguir el carcter KaK
Qemos nue#amente tres literales y el metacaracter ,uma Rus"uemos el patrn en las siguientes cadenas y
#eamos las coincidencias
Badena Boincide !nlisis
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
Para "ue una cadena de caracteres coincida con dicho patrnE
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
-
7/25/2019 Regexp_like en Oracle
4/11
Badena Boincide !nlisis
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
Para "ue una cadena de caracteres coincida con dicho patrnE
N ebe estar el carcter KcK
N .e debe seguir el carcter KaK
N .e debe seguir uno y solamente uno de los siguientes caracteresE KsK$ KnK o KtK
N .e debe seguir el carcter KaK
Badena Boincide !nlisis
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
-
7/25/2019 Regexp_like en Oracle
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 @
Badena Boincide !nlisis
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
Para "ue una cadena de caracteres coincida con dicho patrnE
N ebe estar el carcter KcK
N .e debe seguir el carcter KaK
N .e debe seguir uno y solamente un carcter "ue no sea ni KsK ni KnK ni KtK
N .e debe seguir el carcter KaK
Badena Boincide !nlisis
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
-
7/25/2019 Regexp_like en Oracle
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
Badena Boincide !nlisis
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
Badena Boincide !nlisis
-
7/25/2019 Regexp_like en Oracle
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
Badena Boincide !nlisis
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\*
Badena Boincide !nlisis
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
-
7/25/2019 Regexp_like en Oracle
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
Badena Boincide !nlisis
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
-
7/25/2019 Regexp_like en Oracle
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&"&'#&
-
7/25/2019 Regexp_like en Oracle
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'&"&'#&
-
7/25/2019 Regexp_like en Oracle
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"
top related