Home » SQL & PL/SQL » SQL & PL/SQL » regexp expected (10g,win xp)
regexp expected [message #442747] Wed, 10 February 2010 06:12 Go to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Hi All

Is there any shorter way to write this using regexp/anything
SELECT MAX (v_pos)
  FROM (SELECT INSTR (SUBSTR ('ayushanand; aaa', 1, 20), ' ', -1, 1) v_pos
          FROM DUAL
        UNION
        SELECT INSTR (SUBSTR ('ayushanand; aaa', 1, 20), ',', -1, 1) v_pos
          FROM DUAL
        UNION
        SELECT INSTR (SUBSTR ('ayushanand; aaa', 1, 20), ';', -1, 1) v_pos
          FROM DUAL)
Re: regexp expected [message #442750 is a reply to message #442747] Wed, 10 February 2010 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> with data as (select 'ayushanand; aaa' val from dual)
  2  select length(val)-regexp_instr(reverse(val),'( |,|;)',1)+1 idx
  3  from data
  4  /
       IDX
----------
        12

1 row selected.

Regards
Michel
Re: regexp expected [message #442751 is a reply to message #442750] Wed, 10 February 2010 06:47 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
YOU ROCK
Re: regexp expected [message #442768 is a reply to message #442750] Wed, 10 February 2010 08:06 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Insted of reverse we can use ....

IND> with data as (select 'ayushanand; aaa' val from dual)
  2  select
  3  length(val)-regexp_instr(UTL_RAW.cast_to_varchar2
  4  (UTL_RAW.REVERSE (UTL_RAW.cast_to_raw ('ayushanand; aaa'))),'( |,|;)',1)+1 idx
  5   from data
  6  /

       IDX
----------
        12

1 row selected.


sriram Smile
Re: regexp expected [message #442830 is a reply to message #442768] Wed, 10 February 2010 21:23 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
Is there any shorter way to write this using regexp/anything

Please define you term "shorter" ! In which way? length of code or anything else ?

sriram Smile
Re: regexp expected [message #442996 is a reply to message #442747] Thu, 11 February 2010 23:11 Go to previous messageGo to next message
vv_sabayev
Messages: 2
Registered: February 2010
Location: Russia
Junior Member
try like this

SELECT INSTR (TRANSLATE(SUBSTR ('ayushanand; aaa', 1, 20), ' ,;', ' '), ' ', -1, 1) FROM DUAL

first replace all chars ",;" except ' ' to ' '
then last search ' '
Re: regexp expected [message #442997 is a reply to message #442996] Thu, 11 February 2010 23:18 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
try like this

I tried that...check the answer..
ind> SELECT INSTR (TRANSLATE(SUBSTR ('ayushanand; aaa', 1, 20), ' ,;', ' '), ' ', -1, 1) FROM DUAL 
  2  
ind> /

INSTR(TRANSLATE(SUBSTR('AYUSHANAND;AAA',1,20),',;',''),'',-1,1)
---------------------------------------------------------------
                                                             11

1 row selected.

As you are new member here ...Please see the post guide lines.
Ask the moderators for the forum guide lines page.

sriram Smile
Re: regexp expected [message #443006 is a reply to message #442997] Fri, 12 February 2010 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The result should be 12 as posted in previous answers (including yours!).

As you are a senior member you should:
1/ read the previous posts before replying
2/ post valid solution
3/ post something that adds to the previous posts and not repeat previous solutions with other words (not done here but in other of your posts)

Regards
Michel
Re: regexp expected [message #443011 is a reply to message #443006] Fri, 12 February 2010 00:53 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
The result should be 12 as posted in previous answers (including yours!).

I said ..Quote:
check the answer..As his answer got 11.
.i.e.thats not the OP requirement.

As you are a senior member you should:
1/ read the previous posts before replying.
I didn`t get this proper can you high light the one which i did n`t read the previous post? may be you are saying about yester days topic the "Boolean" Problem topic...after posting that i relaised that one.

2/ post valid solution.
Which one is not valid? in all of my posts

3/ post something that adds to the previous posts and not repeat previous solutions with other words (not done here but in other of your posts)

I guess you are saying about "scott" 100 times......post.

if so there an error in the previous reply i corrected that for 100 lines(what happens if he did that for 100 times a the OP asked for 100 times).

Thanks for the info
Hope this is clear now.

sriram Smile
Re: regexp expected [message #443013 is a reply to message #443011] Fri, 12 February 2010 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
/forum/fa/1597/0/

Regards
Michel
Re: regexp expected [message #443016 is a reply to message #443013] Fri, 12 February 2010 01:03 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
expected answer....
Next one would be from FRANK i guess.

And moderators....IF i did anything wrong with my posts in this forum...On what ever Michel raised..

It would be help ful to me in furthur while posting the answers next time...

All suggetions and feedbacks are welcome...

Hope My answers are not that much worst...if you guys/moderators think in that way let me know.
Thank you
Sriram Smile
Re: regexp expected [message #443017 is a reply to message #442996] Fri, 12 February 2010 01:03 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
try like this

SELECT INSTR (TRANSLATE(SUBSTR ('ayushanand; aaa', 1, 20), ' ,;', ' '), ' ', -1, 1) FROM DUAL

first replace all chars ",;" except ' ' to ' '
then last search ' '


Nice one.
But Michels answer suits my case the best as I have to search for a pattern as a delimiter also, however same thing can be achieved in your case as well by using regexp_replace

create or replace FUNCTION return_last_delimiter (pi_string IN VARCHAR2)
   RETURN NUMBER
IS
   l_temp_number   NUMBER;
BEGIN
   SELECT length(replace(pi_string,CHR(10),',')) 
          - REGEXP_INSTR (REVERSE (replace(pi_string,CHR(10),',')), '( |,|;|<[^<>]+>)', 1) 
            idx
     INTO l_temp_number
     FROM DUAL;
   RETURN l_temp_number;
END return_last_delimiter;

SQL> ed
Wrote file afiedt.buf

  1  WITH DATA AS
  2       (SELECT 'ayush<code>ayush<secondcode>ayush<lastcode>'  val
  3          FROM DUAL)
  4  SELECT substr(val,1,return_last_delimiter (val))
  5*   FROM DATA
SQL> /

SUBSTR(VAL,1,RETURN_LAST_DELIMITER(VAL))
------------------------------------------------------------------------------
ayush<code>ayush<secondcode>ayush

[Updated on: Fri, 12 February 2010 01:18] by Moderator

Report message to a moderator

Re: regexp expected [message #443031 is a reply to message #443017] Fri, 12 February 2010 02:51 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Thanks for the feed back.
But i don`t think that function reverse is documented in 10g.which has some bugs in it.
Any way congrats...

sriram Smile
Re: regexp expected [message #443032 is a reply to message #443031] Fri, 12 February 2010 03:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
which has some bugs in it.

References?

Regards
Michel
Re: regexp expected [message #443033 is a reply to message #443032] Fri, 12 February 2010 03:11 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What, you never saw a bug in Oracle documentation?

Or are you referring to bugs in REVERSE undocumented function? /forum/fa/917/0/
Re: regexp expected [message #443034 is a reply to message #443032] Fri, 12 February 2010 03:13 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://awads.net/wp/2006/08/30/cool-undocumented-sql-function-reverse/


@ Michel IS it really documented...
I even searched at OTN documents page i did n`t found that reverse function in it.
sriram Smile
Re: regexp expected [message #443035 is a reply to message #443034] Fri, 12 February 2010 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
ramoradba wrote on Fri, 12 February 2010 10:13
http://awads.net/wp/2006/08/30/cool-undocumented-sql-function-reverse/
@ Michel IS it really documented...
I even searched at OTN documents page i did n`t found that reverse function in it.
sriram Smile

It is not documented and has no known bug.
The link you mentioned does not show a bug just a misunderstanding of the function from the author.

By the way, it existed since at least version 4 (didn't work on previous versions).

Regards
Michel



Re: regexp expected [message #443042 is a reply to message #443035] Fri, 12 February 2010 04:20 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
thanks
sriram Smile
Re: regexp expected [message #443288 is a reply to message #442997] Sun, 14 February 2010 23:07 Go to previous message
vv_sabayev
Messages: 2
Registered: February 2010
Location: Russia
Junior Member
hi!

this SQL works correctly,
third param of TRANSLATE contains TREE blanks

SELECT INSTR (TRANSLATE(SUBSTR ('ayushanand; aaa', 1, 20), ' ,;', '   ') , ' ', -1, 1) IDX FROM DUAL



IDX
-------
12

[Updated on: Mon, 15 February 2010 00:26]

Report message to a moderator

Previous Topic: How To Get Week Number From specified date.
Next Topic: How to generate textfiles in local machine using procedure
Goto Forum:
  


Current Time: Fri Feb 14 08:32:16 CST 2025