Message-Id: <10736.126222@fatcity.com> From: Jacques Kilchoer Date: Tue, 9 Jan 2001 19:06:16 -0800 Subject: RE: sql help 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@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 ; V1 ------------------------------------------------------------------------= ---- - 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 RE: sql help

> -----Original Message-----
> From: Joan [mailto:tylin@mediaone.net]=
> Sent: mardi, 9. janvier 2001 16:15
> To: Multiple recipients of list ORACLE-L
> Subject: sql help
>
> 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.c= lick.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 ;

V1
---------------------------------------------------------------= --------------
V2
---------------------------------------------------------------= --------------
http://www.cartoonnetwork.com:80/legal/img/boomerang.c= lick.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         &= 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,
 27     substr
 28        = (v1,
 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
-------------------- --------------------
image         &nbs= p;      gif
image         &nbs= p;      jpg

Jacques R. Kilcho=EBr
(949) 754-8816