RE: sql help

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 9 Jan 2001 19:06:16 -0800
Message-Id: <10736.126222@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C07AB2.4C2FC3E0
Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

> -----Original Message-----
> From: Joan [mailto:tylin_at_mediaone.net]
> Sent: mardi, 9. janvier 2001 16:15
> To: Multiple recipients of list ORACLE-L
> Subject: sql help

>=20

> Dear listers,
> I need some sql help

I'm not 100% sure I understood exactly what output you are expecting. = In any
event, here's an example of some string extraction.

SQL> create table t (v1 varchar2 (1000), v2 varchar2 (1000)) ;

Table created.

SQL> insert into t (v1, v2) values
2 =
('http://www.cartoonnetwork.com:80/legal/img/boomerang.click.gif',

  3    'Server: Netscape-Enterprise/2.01' || chr (10) ||
  4    'Expires: Tue, 09 Jan 2001 19:47:10 GMT' || chr (10) ||
  5    'Accept-ranges: bytes' || chr (10) ||
  6    'Last-modified: Fri, 15 Dec 2000 22:50:40 GMT' || chr (10) ||
  7    'Content-type: image/gif') ;

1 row created.

SQL> insert into t (v1, v2) values

  2   ('http://www.coedsexxx.com:80/tgp/posing/body/013.jpg',
  3    'Server: Apache/1.3.12 (Unix)' || chr (10) ||
  4    'Last-Modified: Sat, 12 Aug 2000 12:26:03 GMT' || chr (10) ||
  5    'ETag: "5330-478a-3995425b"' || chr (10) ||
  6    'Accept-Ranges: bytes' || chr (10) ||
  7    'Content-Type: image/jpeg') ;

1 row created.

SQL> set pagesize 100
SQL> select v1, v2 from t ;

------------------------------------------------------------------------=

-
V2

------------------------------------------------------------------------=

-
http://www.cartoonnetwork.com:80/legal/img/boomerang.click.gif Server: Netscape-Enterprise/2.01
Expires: Tue, 09 Jan 2001 19:47:10 GMT
Accept-ranges: bytes
Last-modified: Fri, 15 Dec 2000 22:50:40 GMT Content-type: image/gif

http://www.coedsexxx.com:80/tgp/posing/body/013.jpg Server: Apache/1.3.12 (Unix)
Last-Modified: Sat, 12 Aug 2000 12:26:03 GMT ETag: "5330-478a-3995425b"
Accept-Ranges: bytes
Content-Type: image/jpeg

SQL> column content_type format a20
SQL> column url_extension format a20
SQL> define search_string =3D "CONTENT-TYPE:"
SQL> define search_string_len =3D 13
SQL> define delim1 =3D "/"
SQL> define delim1_len =3D 1
SQL> define delim2 =3D "."
SQL> define delim2_len =3D 1
SQL> set verify off
SQL> select
  2     ltrim
  3       (substr
  4          (substr
  5             (v2,
  6              instr
  7                (upper (v2),
  8                 '&search_string',
  9                 -1
 10                ) + &search_string_len
 11             ),
 12           1,
 13           instr
 14             (substr
 15                (v2,
 16                 instr
 17                   (upper (v2),
 18                    '&search_string',
 19                    -1
 20                   ) + &search_string_len
 21                ),
 22              '&delim1'
 23             ) - &delim1_len
 24          )
 25       )
 26      as content_type,
 27     substr
 28        (v1,
 29         instr
 30           (v1,
 31            '&delim2',
 32            -1
 33           ) + &delim2_len
 34        )
 35      as url_extension

36 from t ;

CONTENT_TYPE URL_EXTENSION

-------------------- --------------------
image                gif
image                jpg

Jacques R. Kilcho=EBr
(949) 754-8816
Quest Software, Inc.

8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com

------_=_NextPart_001_01C07AB2.4C2FC3E0
Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =

charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2652.35">

<TITLE>RE: sql help</TITLE>
</HEAD>
<BODY>

> -----Original Message----- > From: Joan [<A = HREF=3D"mailto:tylin_at_mediaone.net">mailto:tylin_at_mediaone.net</A>]=

<BR><FONT SIZE=3D2>&gt; Sent: mardi, 9. janvier 2001 16:15</FONT>
<BR><FONT SIZE=3D2>&gt; To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>&gt; Subject: sql help</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Dear listers,</FONT>
<BR><FONT SIZE=3D2>&gt; I need some sql help</FONT>
</P>

I'm not 100% sure I understood exactly what output = you are expecting. In any event, here's an example of some string = extraction.

SQL> create table t (v1 varchar2 (1000), v2 = varchar2 (1000)) ;

Table created.

SQL> insert into t (v1, v2) values   2   ('<A = HREF=3D"http://www.cartoonnetwork.com:80/legal/img/boomerang.click.gif'"= =
TARGET=3D"_blank">http://www.cartoonnetwork.com:80/legal/img/boomerang.c= lick.gif'</A>,
   3    'Server: = Netscape-Enterprise/2.01' || chr (10) ||   4    'Expires: Tue, 09 Jan = 2001 19:47:10 GMT' || chr (10) ||   5    'Accept-ranges: bytes' || = chr (10) ||
   6    'Last-modified: Fri, 15 = Dec 2000 22:50:40 GMT' || chr (10) ||   7    'Content-type: = image/gif') ;

1 row created.

SQL> insert into t (v1, v2) values   2   ('<A = HREF=3D"http://www.coedsexxx.com:80/tgp/posing/body/013.jpg'" = TARGET=3D"_blank">http://www.coedsexxx.com:80/tgp/posing/body/013.jpg'</= A>,
   3    'Server: Apache/1.3.12 = (Unix)' || chr (10) ||
   4    'Last-Modified: Sat, 12 = Aug 2000 12:26:03 GMT' || chr (10) ||   5    'ETag: = "5330-478a-3995425b"' || chr (10) ||   6    'Accept-Ranges: bytes' || = chr (10) ||
   7    'Content-Type: = image/jpeg') ;

1 row created.

<P><FONT SIZE=3D2>SQL&gt; set pagesize 100</FONT>
<BR><FONT SIZE=3D2>SQL&gt; select v1, v2 from t ;</FONT>
</P>

V1
 <FONT =

SIZE=3D2>---------------------------------------------------------------=

--------------
 V2
 <FONT =

SIZE=3D2>---------------------------------------------------------------=

--------------
 <A =
HREF=3D"http://www.cartoonnetwork.com:80/legal/img/boomerang.click.gif" = TARGET=3D"_blank">http://www.cartoonnetwork.com:80/legal/img/boomerang.c=

lick.gif</A></FONT>
<BR><FONT SIZE=3D2>Server: Netscape-Enterprise/2.01</FONT>
<BR><FONT SIZE=3D2>Expires: Tue, 09 Jan 2001 19:47:10 GMT</FONT>
<BR><FONT SIZE=3D2>Accept-ranges: bytes</FONT>
<BR><FONT SIZE=3D2>Last-modified: Fri, 15 Dec 2000 22:50:40 GMT</FONT>
<BR><FONT SIZE=3D2>Content-type: image/gif</FONT>

<A =
HREF=3D"http://www.coedsexxx.com:80/tgp/posing/body/013.jpg" = TARGET=3D"_blank">http://www.coedsexxx.com:80/tgp/posing/body/013.jpg</A= >

<BR><FONT SIZE=3D2>Server: Apache/1.3.12 (Unix)</FONT>
<BR><FONT SIZE=3D2>Last-Modified: Sat, 12 Aug 2000 12:26:03 GMT</FONT>
<BR><FONT SIZE=3D2>ETag: &quot;5330-478a-3995425b&quot;</FONT>
<BR><FONT SIZE=3D2>Accept-Ranges: bytes</FONT>
<BR><FONT SIZE=3D2>Content-Type: image/jpeg</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>SQL&gt; column content_type format a20</FONT>

SQL> column url_extension format a20 SQL> define search_string =3D = "CONTENT-TYPE:"

<BR><FONT SIZE=3D2>SQL&gt; define search_string_len =3D 13</FONT>
<BR><FONT SIZE=3D2>SQL&gt; define delim1 =3D &quot;/&quot;</FONT>
<BR><FONT SIZE=3D2>SQL&gt; define delim1_len =3D 1</FONT>
<BR><FONT SIZE=3D2>SQL&gt; define delim2 =3D &quot;.&quot;</FONT>
<BR><FONT SIZE=3D2>SQL&gt; define delim2_len =3D 1</FONT>
<BR><FONT SIZE=3D2>SQL&gt; set verify off</FONT>
<BR><FONT SIZE=3D2>SQL&gt; select</FONT>
<BR><FONT SIZE=3D2>&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp; ltrim</FONT>
<BR><FONT SIZE=3D2>&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =

(substr

<BR><FONT SIZE=3D2>&nbsp; =
4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (substr</FONT>
<BR><FONT SIZE=3D2>&nbsp; =
5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=

; (v2,
   =
6           &nbsp= ;  instr
   =
7           &nbsp= ;    (upper (v2),

<BR><FONT SIZE=3D2>&nbsp; =
8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp; '&amp;search_string',</FONT>
<BR><FONT SIZE=3D2>&nbsp; =
9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp; -1</FONT>

10         &= nbsp;      ) + &search_string_len  11         &= nbsp;   ),
  12         &= nbsp; 1,
  13         &= nbsp; instr
  14         &= nbsp;   (substr
  15         &= nbsp;      (v2,  16         &= nbsp;       instr  17         &= nbsp;         (upper = (v2),
  18         &= nbsp;          = '&search_string',
  19         &= nbsp;          -1  20         &= nbsp;         ) + = &search_string_len
  21         &= nbsp;      ),  22         &= nbsp;    '&delim1'  23         &= nbsp;   ) - &delim1_len  24          = )
  25       = )
  26      as = content_type,

<BR><FONT SIZE=3D2>&nbsp;27&nbsp;&nbsp;&nbsp;&nbsp; substr</FONT>
<BR><FONT SIZE=3D2>&nbsp;28&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
(v1,</FONT>
<BR><FONT =

SIZE=3D2> 29         = instr
  30         &= nbsp; (v1,
  31         &= nbsp;  '&delim2',
  32         &= nbsp;  -1
  33         &= nbsp; ) + &delim2_len
  34        = )
  35      as = url_extension
  36   from t ;

CONTENT_TYPE         = URL_EXTENSION

<BR><FONT SIZE=3D2>-------------------- --------------------</FONT>

image         &nbs= p;      gif image         &nbs= p;      jpg

Jacques R. Kilcho=EBr (949) 754-8816 Received on Tue Jan 09 2001 - 21:06:16 CST