Home » SQL & PL/SQL » SQL & PL/SQL » sql query to find the string
sql query to find the string [message #215706] Tue, 23 January 2007 10:30 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member

tree  cell  col1
 t1    c1   \RP1*(AA1*AA4)\RP1*(AA1*AA5)\RP1*(AA1*AA3)	



from the above data i would like to extract


tree   cell   col1

t1      c1   RP1*(AA1*AA3)   (LAST STRING AFTER '\')

Re: sql query to find the string [message #215709 is a reply to message #215706] Tue, 23 January 2007 10:56 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
select substr(col1, instr(col1, '\', -1) + 1, length(col1)) result 
from your_table;
Re: sql query to find the string [message #215711 is a reply to message #215709] Tue, 23 January 2007 11:05 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
i forgot to mention

data could be like this also

tree  cell  col1
 t1    c1   \RP1*(AA1*AA4)\RP1*(AA1*AA5)\RP1*(AA1*AA3)\\\

	


in that case also it should return

tree  cell  col1
 t1    c1   RP1*(AA1*AA3)
	

Re: sql query to find the string [message #215737 is a reply to message #215711] Tue, 23 January 2007 13:20 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> with t as (
  1	select '\RP1*(AA1*AA4)\RP1*(AA1*AA5)\RP1*(AA1*AA3)\\\' string
  2	from dual)
  3  select substr(rtrim(string, '\'), instr(rtrim(string, '\'), '\', -1) + 1) SUB from t;

SUB
-------------
RP1*(AA1*AA3)


If you are on 10g,

SQL> with t as (
  2     select '\RP1*(AA1*AA4)\RP1*(AA1*AA5)\RP1*(AA1*AA3)\\\' string
  3     from dual)
  4  select regexp_substr(rtrim(string,'\'),'[^\\]+$') SUB from t;

SUB
-------------
RP1*(AA1*AA3)

[Updated on: Tue, 23 January 2007 21:10]

Report message to a moderator

Previous Topic: How to Display a Result based on Conditions?
Next Topic: Analytic with Count
Goto Forum:
  


Current Time: Thu Dec 08 14:29:07 CST 2016

Total time taken to generate the page: 0.05589 seconds