Remove Oracle Special Characters

Asked

Viewed 6,537 times

2

Good morning,

I have a query here made in Oracle where in one of the records is bringing a string starting with quotes, has some function oracle to remove only the quotes at the beginning and at the end of a string?

Thanks in advance.

oracle pl-sql

2 answers

1


Based on your need, I designed a function, and added the possibility for you to define which character you want to remove from the beginning and end of the string, I hope it helps you:

CREATE OR REPLACE FUNCTION F_REMOVE_CARACTER_INICIO_FIM(P_STRING    IN VARCHAR,
                                                        P_CARACTERE IN VARCHAR2 DEFAULT '''')
  RETURN VARCHAR2 IS
  RESULT   VARCHAR2(500);
  V_INICIO BOOLEAN := FALSE;
  V_FINAL  BOOLEAN := FALSE;
-----------------------------------------------------------------------------------
--DESENVOLVEDOR::. RAPHAEL DE SOUZA GODOI
--DT. CRIAÇÃO::. 08/10/2018
-----------------------------------------------------------------------------------
BEGIN

  IF INSTR(P_STRING, P_CARACTERE) = 1 THEN
    V_INICIO := TRUE;
  END IF;

  IF INSTR(P_STRING, P_CARACTERE, -1) = LENGTH(P_STRING) THEN
    V_FINAL := TRUE;
  END IF;

  IF (V_INICIO = TRUE AND V_FINAL = TRUE) THEN
  
    RESULT := SUBSTR(P_STRING, 2, LENGTH(P_STRING) - 2);
  
  ELSIF V_INICIO = TRUE AND V_FINAL = FALSE THEN
  
    RESULT := SUBSTR(P_STRING, 2, LENGTH(P_STRING));
  ELSIF V_FINAL = TRUE AND V_INICIO = FALSE THEN
    RESULT := SUBSTR(P_STRING, 1, LENGTH(P_STRING) - 1);
  ELSE
    RESULT := P_STRING;
  
  END IF;

  RETURN(RESULT);
END F_REMOVE_CARACTER_INICIO_FIM;

1

Browser other questions tagged oracle pl-sql

You are not signed in. Login or sign up in order to post.