Home » SQL & PL/SQL » SQL & PL/SQL » regexp expected (10g,win xp)
regexp expected [message #442747] |
Wed, 10 February 2010 06:12  |
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 #442996 is a reply to message #442747] |
Thu, 11 February 2010 23:11   |
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   |
 |
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
|
|
|
|
|
|
|
Re: regexp expected [message #443017 is a reply to message #442996] |
Fri, 12 February 2010 01:03   |
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 #443288 is a reply to message #442997] |
Sun, 14 February 2010 23:07  |
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
|
|
|
Goto Forum:
Current Time: Fri Feb 14 08:32:16 CST 2025
|