CODEWITHSUNDEEP
sqloracle-databaseoracle11g
daZza
daZza

Reputation: 1689

Replacing a String between two anchor points

I am trying to find a way to replace the string in between two "anchor points" in my VARCHAR2 column.

These "anchors" are <? and ?> and I want to remove (replace with '') everything that is between those two symbols.

I've already tried playing around with the REPLACE() function, e.g. stuff like SELECT REPLACE(my_varchar2_column,'<? % ?>') FROM my_table; and using the % operator as a wildcard, but that didn't work. No error was thrown, but the result wasn't as expected, as in the the replace function interpreting the % literally and not as a wildcard.

Does anyone have an idea how to achieve a replacement like this?

Example for current content of the column:

text I want to keep <? cryptic stuff in betweeen ?> text I want to keep as well

By replacing everything in between <? and ?> I want to remove that whole passage from my columns text. Expected result is like this:

text I want to keep text I want to keep as well

Upvotes: 0

Views: 132

Answers (2)

Yathish Manjunath
Yathish Manjunath

Reputation: 2029

You Can you use REGEXP_REPLACE functionality of ORACLE.

First Argument = The column which needs to be replaced.

Second Argument = the substring to search for replacement.

Third Argument = the text to be replaced ( NOTE : if we omit this argument, the matched substrings are deleted

SELECT 
REGEXP_REPLACE('text I want to keep <? cryptic stuff in betweeen ?> text ','<\?.*\?>') 
FROM DUAL

OUTPUT:

text I want to keep  text

Upvotes: 4

Non Plus Ultra
Non Plus Ultra

Reputation: 906

select substr( my_varchar2_column, 1, INSTR( my_varchar2_column, '') +2 ) from yourtable;

Would that do the trick? You find the position of the beginning and end tags with the INSTR function and use it to SUBSTR the values before and after the tags.

Upvotes: 0

Related Questions