SQL & PL/SQL

select dump(item_desc) from item_test;

Are you actually storing chr(10)

What are you doing differently:

SQL> select *
  from item_test
 where item_desc like '%' || chr (10) || '%'
/
ITEM_DESC                                                               
------------------------------------------------------------------------
AWT-                                                                    
SWING                                                                   
                                                                                                                                  
AWT-S                                                                   
SWING                                                                   
                                                                                                                                  

2 rows selected.

?

Thanks!!!

SELECT * FROM ITEM_TEST WHERE dump(item_desc) like '%10,%'

gives the required output..

but it would fail for sth like

SQL> with t as (
  select chr(90) || chr(110) || chr(100) str from dual
)
--
--
select str from t where dump(str) like '%10,%'
/
STR 
----
Znd 
1 row selected.

;)

one more query in the same.

One more clarification...

now i am going to inserr one more record:

insert into item_test values('ZAWS-
SWING-
JAVA');

In this case teh field contains 3 lines.

Now i want to retrieve only the multi line records which contains only 2 lines excluding the recently inserted one since it contains 3 lines.

Thanks

I think the below will satisfy the requirement:

SELECT * FROM ITEM_TEST WHERE DUMP(ITEM_DESC) LIKE '%,10,%'

Edited by: 858433 on May 12, 2011 6:32 AM

To calculate no of EOL in field compare original field length with the value where EOL is replaced with null

with aa as
(select ' dasda
;
;' as mytext
from dual
union all
select 'texts
' from dual
)
, outp as
(
select aa.*, length(aa.mytext) len_a, length(replace(aa.mytext,chr(10),''))as len_b
from aa
)
select mytext, len_a-len_b no_of_eol
from outp

No:

SQL> with t as (
  select chr(90) || chr(110) || chr(10) str from dual
)
--
--
select str from t where dump(str) like '%,10,%'
/
no rows selected.

I'd still go for either INSTR or LIKE '%' || CHR(10) || '%' ;

Can you please elobarate according to my sample table and records?

Thanks

Then what about

select str from t where dump(str) like '%,10%'

it will give right?

(Missed something obvious)

Edited by: William Robertson on May 13, 2011 8:40 AM

dump(str) like'%,10%' will give the multiline fields since 10 is the asci value for new line character.

While I like the attempts to use DUMP (It's one of my favorite documented functions to pull out when I am at database cocktail parties -- I really livens up the evening!), I don't think that DUMP works here. The problem is that the models are relying on the presence of a comma immediately before and after the "10" in order to make sure that it does not also pull in other character with three digits, two of which happen to be "10" ( for example, Character 105 is lower case "i" and 210 is capital "O" with oblique accent). But, by requiring that those commas surround the "10" in the output from DUMP, you are excluding records where the newline character is either first or last.

I have faced many, many challenges from imported data where a chr(10) or a chr(13) was the very last character in the string! It is vey common.

As for the question of finding those cases with one line feed but not two, if you are on 11g, you can do it very easily with REGEXP_COUNT.

WHERE REGEXP_COUNT ( myfieldvalue, CHR(10)) = 1

Apologies, I totally missed that it was checking the output of DUMP(). It was late...

You can keep on adding to the like clause to check for multiple occurrences.

select * from item_test where item_desc like '%' || chr(10) || '%' || chr(10) || '%'
;

will check for strings containing 2 new lines, you could exclude items with 2 lines by simple saying not like.

>
> This will also fail for the case :
with t as (
select chr(90) || chr(110) || chr(100) str from dual
)
select str from t where dump(str) like '%,10%'

Znd

1 row returned.

So, the refined one is ,


with t as (
select chr(90) || chr(110) || chr(100) str from dual
*)*
select str from t where dump(str) like '%,10%' and Not ( dump(str) like '%100%')
No rows returned..

Let me know if this fails for any case

But we are using Oracle 8i where there are no regular expression in built functions like REGXP_COUNT

Why do you insist in using DUMP?
Again your query would fail for charriage return at the beginning of the string:

SQL> with t as (
  select chr(10) || chr (90) || chr (110) || chr (100) str from dual
)
--
--
select str
  from t
 where dump (str) like '%,10%'
   and not (dump (str) like '%100%')
/
no rows selected.

Thanks Michael!!

Please let me know about the below one:

with t as (
select chr(10) || chr(110) || chr(100) str from dual
)
select str from t where (dump(str) like '%,10%') or (dump(str) like '%10,%') and Not ( dump(str) like '%100%')

nd

1 row selected

let me know if it fails for any case

again: why are you insisting on DUMP???

No row should be returned in the following:

SQL> with t as (
  select chr(110) || chr(110) str from dual
)
--
--
select str from t where (dump(str) like '%,10%') or (dump(str) like '%10,%') and Not ( dump(str) like '%100%')
/
STR
---
nn 
1 row selected.

But
select * from item_test where item_desc like '%' || chr(10) || '%' || chr(10) || '%'

will return the records which consist of 2 or more new line characters.

but how i can make this to return the records with exactly 2 new line charcters and not more than that?

Thanks

Edited by: Pandeesh on May 13, 2011 2:15 AM

Probably it should be like

...
 where dump (str) like '%,10,%'
    or dump (str) like '% 10,%'
    or dump (str) like '%,10'

The idea is as extensible as you want - just add something like...

select * from item_test where item_desc like '%' || chr(10) || '%' || chr(10) || '%'
       and item_desc not  like '%' || chr(10) || '%' || chr(10) || '%' || chr(10) || '%'
;

select *
  from table1
 where length (col) - length (replace (col, chr (10))) = 2
/

Hi Michael,

Can u pls explain with sample data?

Thanks

SQL> with t as (
  select 'Some text with one' || chr(10) || 'carriage return' str from dual union all
  select 'Some text ' || chr(10) || ' with two'   || chr(10) || 'carriage return' str from dual union all
  select 'Some text ' || chr(10) || ' with three' || chr(10) || 'carriage '||chr(10)||' return' str from dual
)
--
--
select * from t
 where length (str) - length (replace (str, chr (10))) = 2
/
STR                                                         
------------------------------------------------------------
Some text                                                   
 with two                                                   
carriage return                                             
                                                                                
1 row selected.

Hi,

Recently i have thought about another scenario::

For example i am inserting the below record:;

INSERT INTO ITEM_TEST values('AB
');

1 row inserted..

But i dont want to dispaly this record while selecting.

Even though there is a CHR(10) in this record, there are no characters followed by CHR(10).

Now i am inserting another record

INSERT INTO ITEM_TEST values('AB
CD');

But i want to display this since there are some characters following by CHR(10).

Finally i achieve this by the below queries:

select * from item_test where item_desc like '%' || CHR(10) || '%' and

LENGTH(SUBSTR(ITEM_DESC,INSTR(ITEM_DESC,CHR(10),1,1))) > 1

select * from item_test where item_desc like '%' || CHR(10) || '%' and

SUBSTR(ITEM_DESC,INSTR(ITEM_DESC,CHR(10),1,1)) != CHR(10)

Let me know your comments and please share if you have any other way to achieve this.

Thanks for your support!!

Edited by: Pandeesh on May 16, 2011 12:47 AM

Edited by: Pandeesh on May 16, 2011 12:49 AM

like this?

SELECT *
FROM ITEM_TEST 
WHERE instr(ITEM_DESC,chr(10))>0
AND SUBSTR(ITEM_DESC,LENGTH(ITEM_DESC)-1,1) != chr(10)

Vivek L

yes Vivek!! Gud!