Home » SQL & PL/SQL » SQL & PL/SQL » How to trim the last matching string using regexp_substr
How to trim the last matching string using regexp_substr [message #631915] Wed, 21 January 2015 15:15 Go to next message
oracleso
Messages: 1
Registered: January 2015
Location: United States
Junior Member
I am trying to use regexp_substr to retrieve string within [!< & >!]. I have below statement but it returned ' To test with regexp_substr.>!]'. How can I exclude >!] and return just the string?

select regexp_substr('[!< To test with regexp_substr.>!]','[^[!<]+>!]') from dual

Report message to a moderator

Re: How to trim the last matching string using regexp_substr [message #631925 is a reply to message #631915] Thu, 22 January 2015 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It depends on the actual problem.
If we strictly follow the example you gave you can use (from the less general to the most one):
SQL> with data as (select '[!< To test with regexp_substr.>!]' v from dual)
  2  select substr(v, 4, length(v)-7) new_v from data;
NEW_V
---------------------------
 To test with regexp_substr

or
SQL> with data as (select '[!< To test with regexp_substr.>!]' v from dual)
  2  select substr(v, 4, instr(v,'>!]')-4) new_v from data;
NEW_V
----------------------------
 To test with regexp_substr.

or
SQL> with data as (select '[!< To test with regexp_substr.>!]' v from dual)
  2  select substr(v, instr(v,'[!<')+4, instr(v,'>!]')-instr(v,'[!<')-4) new_v from data;
NEW_V
---------------------------
To test with regexp_substr.

No need of regexp.

Report message to a moderator

Re: How to trim the last matching string using regexp_substr [message #631926 is a reply to message #631925] Thu, 22 January 2015 00:53 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Using TRANSLATE and TRIM.

SQL> WITH DATA AS
  2    ( SELECT '[!< To test with regexp_substr.>!]' str FROM dual
  3    )
  4  SELECT str, trim(TRANSLATE(str,'[!<>]',' ')) str_new FROM DATA
  5  /

STR                                STR_NEW
---------------------------------- ----------------------------------
[!< To test with regexp_substr.>!] To test with regexp_substr.

SQL>

Report message to a moderator

Re: How to trim the last matching string using regexp_substr [message #631928 is a reply to message #631926] Thu, 22 January 2015 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No need of trim (which in addition remove the first space which is part of the data):
SQL> with data as (select '[!< To test with regexp_substr.>!]' v from dual)
  2  select translate(v, 'x[!<>]', 'x') new_v from data;
NEW_V
----------------------------
 To test with regexp_substr.

[Updated on: Thu, 22 January 2015 01:00]

Report message to a moderator

Re: How to trim the last matching string using regexp_substr [message #631929 is a reply to message #631928] Thu, 22 January 2015 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or with just trim functions:
SQL> with data as (select '[!< To test with regexp_substr.>!]' v from dual)
  2  select ltrim(rtrim(v, '>!]'), '[!<') new_v from data;
NEW_V
----------------------------
 To test with regexp_substr.

[Updated on: Thu, 22 January 2015 01:04]

Report message to a moderator

Re: How to trim the last matching string using regexp_substr [message #631930 is a reply to message #631928] Thu, 22 January 2015 01:06 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Thu, 22 January 2015 12:30

No need of trim


Correct. I missed that OP mentioned

Quote:
retrieve string within [!< & >!].


So the first space is part of the string.

Report message to a moderator

Re: How to trim the last matching string using regexp_substr [message #631931 is a reply to message #631930] Thu, 22 January 2015 01:20 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Or with just REPLACE functions:

SQL> WITH DATA AS
  2    ( SELECT '[!< To test with regexp_substr.>!]' str FROM dual
  3    )
  4  SELECT str, REPLACE(REPLACE(str,'[!<'),'>!]') str_new FROM DATA
  5  /

STR                                STR_NEW
---------------------------------- ----------------------------------
[!< To test with regexp_substr.>!]  To test with regexp_substr.

SQL>


TRANSLATE seems most simple approach in OP's case.

[Updated on: Thu, 22 January 2015 01:21]

Report message to a moderator

Re: How to trim the last matching string using regexp_substr [message #631933 is a reply to message #631931] Thu, 22 January 2015 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
TRANSLATE seems most simple approach in OP's case.


No, it is not adequate as TRANSLATE does not take into account the order of the characters, in addition it'll also remove the characters from the data.
Same thing for LRTRIM for the first point.

Report message to a moderator

Re: How to trim the last matching string using regexp_substr [message #631963 is a reply to message #631929] Thu, 22 January 2015 07:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Thu, 22 January 2015 02:03

Or with just trim functions:


Not exactly:

SQL> with data as (select '[!< Don''t forget text can end with an exclamation sign!>!]' v from dual)

  2  select ltrim(rtrim(v, '>!]'), '[!<') new_v from data
  3  /

NEW_V
---------------------------------------------------
 Don't forget text can end with an exclamation sign

SQL>


SY.

Report message to a moderator

Re: How to trim the last matching string using regexp_substr [message #631969 is a reply to message #631963] Thu, 22 January 2015 07:59 Go to previous message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Indeed, so exact same problem with LRTRIM than with TRANSLATE, none are better both are wrong. Smile

Report message to a moderator

Previous Topic: Populate List from Java
Next Topic: Distributing a total amount (payment) according to individual records (requests)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 04:41:34 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.