Home » SQL & PL/SQL » SQL & PL/SQL » Substr & Instr
Substr & Instr [message #198838] Wed, 18 October 2006 15:27 Go to next message
lowcash75
Messages: 67
Registered: February 2006
Member
I have a column with the following data:
<IMG WIDTH="90%" SRC="http://www.yahoo.com/proposal1_1.gif">

I'm trying to extract the url from the column.


So far I can get to: http://www.yahoo.com/proposal1_1.gif">

I need to remove the '">" also.

Here's is the sql I'm running:
SELECT '<IMG WIDTH="90%" SRC="http://www.yahoo.com/proposal1_1.gif">',
SUBSTR('http://www.yahoo.com/proposal1_1.gif">',INSTR('http://www.yahoo.com/proposal1_1.gif">','"http',1,1) +1)
FROM dual;

Thanks!


Re: Substr & Instr [message #198840 is a reply to message #198838] Wed, 18 October 2006 16:41 Go to previous messageGo to next message
lowcash75
Messages: 67
Registered: February 2006
Member
Figured it out!
here's the solution
SELECT '<IMG WIDTH="90%" SRC="http://www.yahoo.com/proposal1_1.gif">',
SUBSTR('http://www.yahoo.com/proposal1_1.gif">',INSTR('http://www.yahoo.com/proposal1_1.gif">','"http',1,1),
INSTR('http://www.yahoo.com/proposal1_1.gif">','">',-1,1)-INSTR('http://www.yahoo.com/proposal1_1.gif">','"http',1,1)-1)
FROM dual
/
Re: Substr & Instr [message #198841 is a reply to message #198840] Wed, 18 October 2006 16:44 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
I think RTRIM would be a lot cleaner:

sql>with url as (SELECT '<IMG WIDTH="90%" SRC="http://www.yahoo.com/proposal1_1.gif">' value from dual)
  2    select value original, rtrim(substr(value, instr(value, '"http', 1, 1) + 1), '">') substring
  3      from url;

ORIGINAL
------------------------------------------------------------
SUBSTRING
------------------------------------------------------------
<IMG WIDTH="90%" SRC="http://www.yahoo.com/proposal1_1.gif">
http://www.yahoo.com/proposal1_1.gif
Re: Substr & Instr [message #198848 is a reply to message #198841] Wed, 18 October 2006 17:45 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Just be sure to remember that rtrim removes a set of characters from the string - not a substring...

SQL> select rtrim ('pqrst.txt', '.txt'),
  2         rtrim ('pqrst.txt', 'x.t')
  3  from dual;

RTRI RTRI
---- ----
pqrs pqrs
Re: Substr & Instr [message #198849 is a reply to message #198848] Wed, 18 October 2006 17:53 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Yes, that is a good reminder...
Re: Substr & Instr [message #204761 is a reply to message #198848] Wed, 22 November 2006 00:20 Go to previous message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member

its very great example.

cheers
pravin
Previous Topic: TRIM
Next Topic: GLOBAL TEMPORARY TABLES
Goto Forum:
  


Current Time: Sat Dec 10 16:38:22 CST 2016

Total time taken to generate the page: 0.18134 seconds