Good morning,
I have a query here made in Oracle where in one of the records is bringing a string starting with quotation marks, does it have any oracle function to remove only the quotation marks at the beginning and end of a string?
Thank you in advance.
Based on your need, I created a function, and I added the possibility of you defining which character to remove from the beginning and end of the string, I hope it helps:
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;You can do this using REGEX
REGEXP_REPLACE ('"Teste"', '^("|'')|("|'')$', '')
where it replaces single or double quotation marks at the beginning and / or end of its field.