Home » SQL & PL/SQL » SQL & PL/SQL » REPLACE(column,chr(10),'/r')) does not work
| REPLACE(column,chr(10),'/r')) does not work [message #568799] |
Tue, 16 October 2012 13:00  |
 |
annenoe
Messages: 4
Registered: October 2012
Location: Mountain View, CA
|
Junior Member |
|
|
First - newbie - please excuse me if I make a newbie mistake
Second - I'm not an uber developer - just enough SQL to be dangerous
Problem:
I am doing simple search to report across two systems when fields are not identical (they are supposed to be). Then I report the data. I am outputting the data in spool files that we'll post so we can see the details. I am forcing .xls extension so they can be opened in Excel. I am building a tab-delimited file, e.g., sys1.first_name||chr(9)||sys2.first_name.
Everything is working fine with one exception. The problem is there are some records with embedded tab and return characters in the data fields that will (obviously) screw up the data when opened by Excel. e.g., the embedded tab pushes the data out one column, the return splits the record. NOTE: I do NOT want to clean the data. Part of what I'm reporting is that one system is removing tabs and returns and the other is not. So I want these to report.
I was able to successfully do this: replace(sys1.first_name,chr(9),'/t')||chr(9)||replace(sys1.first_name,chr(9),'/t') which then replaces the embedded tab with the '/t' so we can see why it failed the match. But when I tried replace(sys1.first_name,chr(10),'/r') it doesn't work. It puts in the /r but keeps the return character. I know chr(10) is correct b/c select first_name from customer where first_name like '%'|| chr(10) ||'%'; returns the data. But when I do select replace(first_name,chr(10),'/r') from customer where first_name like '%'|| chr(10) ||'%'; it keeps the return character (the name still takes up two lines) then just puts the '/r' right after the return char.
Can someone explain what I'm doing wrong here?
|
|
|
|
|
|
|
|
|
|
| Re: REPLACE(column,chr(10),'/r')) does not work [message #568804 is a reply to message #568803] |
Tue, 16 October 2012 13:30  |
|
annenoe
Messages: 4
Registered: October 2012
Location: Mountain View, CA
|
Junior Member |
|
|
sorry - OS? PL/SQL 11.2.3.0? I'm using Toad if that helps.
So when I do the replace tab, I end up with "Jason/tM." When I replace a return character, I was expecting "Jason/rM.", i.e., the returns characters are removed when I spool the data. As I said, replace(first_name,chr(9),'/t') correctly removes the tab. But when I do replace(first_name,chr(10),'/r') it does *not* remove the return. It puts in the /r but keeps the return. Just don't understand why the tab works but the return does not.
|
|
|
|
|
|
|
|
| Re: REPLACE(column,chr(10),'/r')) does not work [message #568808 is a reply to message #568806] |
Tue, 16 October 2012 14:23 |
|
annenoe
Messages: 4
Registered: October 2012
Location: Mountain View, CA
|
Junior Member |
|
|
Again, sorry.
FIRST_NAME
"Typ=1 Len=14: 67,97,115,101,110,105,13,10,76,105,110,101,32,50"
Now I see it! I didn't know about the dump command. CookieMonster,replace(last_name,chr(13)||chr(10),'/r') works.
Thanks so much.
|
|
|
|
|
|
Goto Forum:
Current Time: Fri May 16 15:51:13 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
