Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Help with line feed line break characters

 
Carrie Davis
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 140
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That looks suspiciously like VB...
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64631
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Michael Matola
whippersnapper
Ranch Hand
Posts: 1817
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1817
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ...
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic