Oracle procedure question related to ID: 27979957

I had a question answered for parsing characters out of a string etc....I want to enhance this query further. I found that in certain circumstances there is a part of a string that starts with { , has text after it, and ends with a }....What I need to do is take this whole portion out of the text being returned. Below is the function that I need this logic added too, if I can....

regexp_replace(col1,'[^a-zA-Z0-9: ' || chr(10) || chr(13)|| '-]');

Example of data:

Jones played '~(`|¤|football)~' and baseball at '~(¦%|¥|Mobile County Training School)~' in '~(`|¤|{link:10098}|Mobile, Alabama,)~' and Alabama A&M University.

The above function will return this

Jones played football and baseball at Mobile County Training School in {link:10098} Mobile, Alabama, and Alabama A&M University.

I need everything between { and } , including the curly brackets, removed...

Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
See if this is what you are after:
select regexp_replace(col1,'{.*}|[^a-zA-Z0-9: ' || chr(10) || chr(13)|| '-]') from tab1;
jknj72Author Commented:
Yes that worked but I do have a question before I finish this. I need to keep certain characters in the text field like periods and back slashes, etc... Is there any way I can include what characters to keep and not get rid of?
jknj72Author Commented:
Actually I just included the characters in the 2nd argument and it worked. I think Im good....For now anyways...
slightwv (䄆 Netminder) Commented:
>>Is there any way I can include what characters to keep and not get rid of?

Yes.  I mentioned this in the previous questions, just add the characters you want to keep to the list inside the square brackets (the '-' MUST be the last one in the list):  [^a-zA-Z0-9: ' || chr(10) || chr(13)|| '-]

The potential issue is if the 'keep' characters are part of the string in the 'special' characters you want to remove.

If you run into this, we may need a more complex regular expression.

Keep a list of the edge case's data and if you need to ask a new question, provide ALL the sample data and expected results for each.
jknj72Author Commented:
will do, thanks slight...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.