PPRuNe Forums - View Single Post - SQL and embedded carriage returns

14th January 2012 | 22:55

#2 (permalink)

mixture

Joined: Aug 2002

Posts: 3,663

Likes: 0

From: Earth

henrybluebottle,

Oracle is cool ! Don't let the open-source naysayers tell you MySQL can do the same things... its an awesome, powerful product ! Plus there's no excuse about the money element, since XE is free !

But I digress.... here's one example....

Oracle Database 11g 11.2.0.2.0 - 64bit Production

create table mytest(idx number(1,0),mycol varchar2(5));
insert into mytest(idx,mycol) values ('1','a');
insert into mytest(idx,mycol) values ('2','b'||chr(13));

select dump(mycol,10) from mytest;
DUMP(MYCOL,10)
-----------------------------------------
Typ=1 Len=1: 97
Typ=1 Len=2: 98,13

Code:

select idx from mytest where substr(mycol,2,1)=chr(13);

      IDX
----------
        2

Or another....

Code:

 select idx from mytest where regexp_like(mycol,chr(13));

      IDX
----------
        2

Told you Oracle was the gift that keeps on giving....

Code:

select idx from mytest where (case when instr(mycol,chr(13))>0 then 'Y' else 'N' end)='Y';

      IDX
----------
        2

Last edited by mixture; 14th January 2012 at 23:46.

Reply

0