aspose file tools*
The moose likes General Computing and the fly likes Help with line feed line break characters Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Engineering » General Computing
Bookmark "Help with line feed line break characters" Watch "Help with line feed line break characters" New topic
Author

Help with line feed line break characters

Carrie Davis
Greenhorn

Joined: Mar 23, 2004
Posts: 4
I am attempting to pull address out of a Siebel database, however some the addresses have line break & line feed characters, or just line break characters, or just line feed characters.
Currently, I am using this:
substr(addr, 1, (decode(instr(addr, CHR(13)||CHR(10)), 0, length(addr)+1, instr(addr, CHR(13)||CHR(10))))-1)
However, I need to check for just CHR(13) or just CHR(10) also. Has anyone ever done this before - can you help me?
Thank you-
Carrie Davis
eammon bannon
Ranch Hand

Joined: Mar 16, 2004
Posts: 140
That looks suspiciously like VB...
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60048
    
  65

In any case, it's certainly not a JDBC question. I'm going to move it to the General Computing forum in case anyone has experience that can help you.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1739
    
    2
It's sufficiently almost-SQLish if you ask me.
I did something similar in Oracle once. I had to extract data to a flat file to be processed by an external program, then reload the data back into an Oracle table. Trouble was that the linefeed/carriage returns in the data resulted in line breaks in the flat file, which the program wasn't equiped to deal with.
Dug out some notes. I pulled one of these:
replace ( i.myfield , chr( 13 ) || chr( 10 ) , '|' )
within my SQL select statement field list. This converted the linefeed/carriage return into a pipe symbol. Later I used a similar approach to convert the pipe symbol back into linefeed/carriage return. (There were no "real" pipe characters in the data.) Hokey as all get-out, but it worked.
Anyhow, why don't you try nesting replace() statements. Something along the lines of
replace ( replace ( i.myfield , chr( 13 ) , '' ) , chr( 10 ) , '' )
(Note that I haven't tested the above. Let folks know if it works.)
Carrie Davis
Greenhorn

Joined: Mar 23, 2004
Posts: 4
Thank you for your reply.
I had to actually take the address line, and split it into two or three substrings depending on the number of line breaks/line feeds. So, I could not use a replace, as this puts everything in the same field. This is how I solved the problem.
Line One is extracted using:
select case
when instr(addr, CHR(13)||CHR(10)) > 0
then substr(addr, 1, (decode(instr(addr,CHR(13)||CHR(10)), 0, length(addr)+1, instr(addr, CHR(13)||CHR(10))))-1)
when instr(addr, CHR(13)) > 0
then substr(addr, 1, (decode(instr(addr,CHR(13)), 0, length(addr)+1, instr(addr, CHR(13))))-1)
when instr(addr, CHR(10)) > 0
then substr(addr, 1, (decode(instr(addr,CHR(10)), 0, length(addr)+1, instr(addr, CHR(10))))-1)
else addr end
Line 2 extracted using:
select case
when instr(addr, CHR(13)||CHR(10)) > 0
then decode(instr(addr,CHR(13)||CHR(10)), 0, '', substr(addr, instr(addr, chr(13)||chr(10))+2))
when instr(addr, CHR(13)) > 0
then decode(instr(addr,CHR(13)), 0, '', substr(addr, instr(addr, chr(13))+1))
when instr(addr, CHR(10)) > 0
then decode(instr(addr,CHR(10)), 0, '', substr(addr, instr(addr, chr(10))+1))
else '' end
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1739
    
    2
Here's splitting into up to 2 substrings using replace():
select case
when instr( replace ( replace ( replace ( addr , chr( 13 ) , '|' ) , chr( 10 ) , '|' ) , '||' , '|' ) , '|' ) > 0
then substr( replace ( replace ( replace ( addr , chr( 13 ) , '|' ) , chr( 10 ) , '|' ) , '||' , '|' ) , 1 ,
instr( replace ( replace ( replace ( addr , chr( 13 ) , '|' ) , chr( 10 ) , '|' ) , '||' , '|' ) , '|' ) - 1 )
else addr end "line1" ,
case
when instr( replace ( replace ( replace ( addr , chr( 13 ) , '|' ) , chr( 10 ) , '|' ) , '||' , '|' ) , '|' ) > 0
then substr( replace ( replace ( replace ( addr , chr( 13 ) , '|' ) , chr( 10 ) , '|' ) , '||' , '|' ) ,
instr( replace ( replace ( replace ( addr , chr( 13 ) , '|' ) , chr( 10 ) , '|' ) , '||' , '|' ) , '|' ) + 1 )
else '' end "line2"
from ...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Help with line feed line break characters
 
Similar Threads
How do i call a Java function within JSP
Problem in executing Oracle Function from JSP Page
PreparedStatement is not renderting char(13)/char(10)
ORA-06550: line 1, column 36: PLS-00103: Encountered the symbol ";"
parse StringBuffer