REGEXP_REPLACE SQL / ORACLE

Question

REGEXP_REPLACE SQL / ORACLE

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

Good I am trying to replace certain characters of a string belonging to a field in my database. 352-6325632 Serially Replaced (352) -632-5632 when using the regexp_replace function of oracle the second parameter is always equal to the expected output. How could I make the next expression (352) -632-5632

  SELECT EMPLEADO.CODIGO Codigo,EMPLEADO.APELLIDO || ',' || 
   EMPLEADO.NOMBRE "Nombres",
    REGEXP_REPLACE(CLIENTE.TELEFONO, '^\d{3}-\d{7}$','')  
 "REGEXP_REPLACE",CLIENTE.TELEFONO
   FROM EMPLEADO JOIN CLIENTE ON(EMPLEADO.CODIGO = 
  CLIENTE.CODIGO_REP_VENTAS) WHERE LENGTH(CLIENTE.TELEFONO)=11;

sql oracle plsql

asked by Danserver 21.05.2018 в 18:22

source

2 answers

Problems with dynamic selection in angular 5 problem tcpdf data one on top of another

score 0 · Answer 1

Use the following expression in the SELECT query:

REGEXP_REPLACE (CLIENTE.TELEFONO, 
                '([[:digit]]{3})-([[:digit]]{3})([[:digit]]{4})', 
                '()--')

In the second parameter of REGEXP_REPLACE you must indicate the regular expression that you are looking for, and in the third parameter you must indicate the output format. The parentheses in the second parameter are used to group the result of the regular expression, which will be used in the third parameter as \ 1, \ 2, \ 3, etc ..

For more information, see the following web .

score 0 · Answer 2

Replacing the field CLIENTE.TELEFONO with the example data 352-6325632 would have the expression:

SELECT REGEXP_REPLACE('352-6325632', '^\d{3}-\d{7}$','') FROM DUAL

and since you have nothing in the chain of replacements, then the boss replaces you for nothing.

Use the parentheses to group and store the expression () and \ 1 to call them, to include them would look like this:

SELECT REGEXP_REPLACE ('352-6325632', '^(\d{3})-(\d{7})$', '()-') FROM DUAL

(352) -6325632

Finally you adjust the expression and the replacement chain according to your need

SELECT REGEXP_REPLACE ('352-6325632', '^(\d{3})-(\d{3})(\d{4})$', '()--') FROM DUAL

(352) -632-5632

You would also do it with:

SELECT REGEXP_REPLACE ('352-6325632', '^(.*)-(\d{3})(.*)$', '()--')  FROM DUAL