Removing LF with Oracle

Question

Issue: I have LF breaks in a description field which is causing issues when I send to a subsystem for upload.

Research: After looking over this site and others, I found many suggestions but none of them have worked thus far

Oracle REPLACE() function isn't handling carriage-returns & line-feeds

Below is what I currently have in my query, but again, the LFs seem to be an issue as they are not being replaced.

REPLACE(REPLACE(field,chr(10),''),chr(13),'')

I am fairly novice, so I am sure I am overlooking something simple. Any help is appreciated.

SELECT DISTINCT 
       LINES.Field1,
       LINES.Field2,
       HEADER.Field1,
       HEADER.Field2,
       translate(HEADER.Field3, chr(10)||chr(11)||chr(13), '    ')

  FROM    REP.LINES
       INNER JOIN
          REP.HEADER
       ON (LINES.INV_ID = HEADER.INV_ID)
WHERE     (LINES.CLASSIFICATION IN
                        ('1',
                         '2',
                         '3',
                         '4'))

      AND (

       (LINES.Random1 IS NOT NULL)
       OR ( LINES.Random2 = 'Value1'
                      AND HEADER.Field3 IS NOT NULL))

                 AND (HEADER.Date BETWEEN TO_DATE (
                                                      '2015-01-01 00:00:00',
                                                      'yyyy/mm/dd hh24:mi:ss')
                                               AND TO_DATE (
                                                      '2015-09-11 00:00:00',
                                                      'yyyy/mm/dd hh24:mi:ss'))

I am very sure that replaces line feeds. Just tested and it works. How come you think it doesn't? — Patrick Hofman, Sep 11 '15 at 13:15
As it stands, this question is a direct duplicate of [the existing question you linked to](http://stackoverflow.com/questions/407027/oracle-replace-function-isnt-handling-carriage-returns-line-feeds). You'll need to add some details to explain how you're testing, and how your situation is different from the ones already addressed. Otherwise, you're just asking the same question and hoping for different answers. — IMSoP, Sep 11 '15 at 13:17
Variations of this question have been asked. I personally like the translate function solution here, http://stackoverflow.com/questions/16407135/oracle-regexp-to-replace-n-r-and-t-with-space. — Patrick Bacon, Sep 11 '15 at 13:46
@PatrickHofman when i open up the file with Notepad++ the LF breaks are still there in the field. — hansolo, Sep 11 '15 at 13:56
@IMSoP none of the suggestions in the previous linked post worked for me. In every instance, once I export the data to a CSV file the LF breaks are still present in the field noted. If it is more appropriate I will continue to add to the question I quoted. — hansolo, Sep 11 '15 at 14:01
@PatrickBacon testing select translate(your_column, chr(10)||chr(11)||chr(13), ' ') from your_table; tried below yesterday and it did not work: TRANSLATE (field, 'x'||CHR(10)||CHR(13), 'x') — hansolo, Sep 11 '15 at 14:07
@PatrickHofman SELECT DISTINCT field1, field2, field3, translate(field4, chr(10)||chr(11)||chr(13), ' ') FROM table; **Output:** 12345,1,XXXXXXXXXX,,123456,XXXXXXXX,"XXXXXX (XXX - XXX1234) -LF- 1. XXXXXXXXXXX XXXXXXXX -LF- 2. XXXXXXXXXXXXXXXX (12345) -LF- 3. XXXXXXXXXXXXXXXXXXXXXX -LF- — hansolo, Sep 11 '15 at 14:21
Please add it in the question. Also, can you set up a SQL fiddle for example? — Patrick Hofman, Sep 11 '15 at 14:26
It would be better if you added the sample data and query into your question, so that it can be formatted correctly and more easily read and understood. — Boneist, Sep 11 '15 at 14:27
@Boneist added a masked version of the query to the question. — hansolo, Sep 11 '15 at 14:44
@DaveMelillo How are you exporting the data to your CSV file? perhaps the issue isn't with the data, but with the method of extraction. — Sentinel, Sep 11 '15 at 16:33

Boneist · Accepted Answer · 2015-09-11T16:02:50.277

It works for me. Perhaps you should use the dump() function to work out the contents of your text field, to see if there are any 10's or 13's in the text?

eg.

select dump(str) str_dump,
       dump(replace(replace(str, chr(10)), chr(13))) replaced_str_dump
from   (select 'ab'||chr(10)||chr(13)||'cd' str from dual);

STR_DUMP                        REPLACED_STR_DUMP        
------------------------------- -------------------------
Typ=1 Len=6: 97,98,10,13,99,100 Typ=1 Len=4: 97,98,99,100

Ok, with the data from your dump supplied in the comments below:

with test_data as (select chr(77)||
                          chr(79)||
                          chr(66)||
                          chr(73)||
                          chr(76)||
                          chr(69)||
                          chr(32)||
                          chr(80)||
                          chr(72)||
                          chr(79)||
                          chr(78)||
                          chr(69)||
                          chr(32)||
                          chr(66)||
                          chr(73)||
                          chr(76)||
                          chr(76)||
                          chr(10)||
                          chr(40)||
                          chr(73)||
                          chr(76)||
                          chr(76)||
                          chr(67)||
                          chr(32)||
                          chr(32)||
                          chr(76)||
                          chr(73)||
                          chr(78)||
                          chr(79)||
                          chr(32)||
                          chr(48)||
                          chr(56)||
                          chr(48)||
                          chr(45)||
                          chr(51)||
                          chr(50)||
                          chr(52)||
                          chr(50)||
                          chr(45)||
                          chr(49)||
                          chr(52)||
                          chr(48)||
                          chr(52)||
                          chr(44)||
                          chr(32)||
                          chr(75)||
                          chr(75)||
                          chr(32)||
                          chr(48)||
                          chr(56)||
                          chr(48)||
                          chr(45)||
                          chr(57)||
                          chr(49)||
                          chr(57)||
                          chr(56)||
                          chr(45)||
                          chr(51)||
                          chr(51)||
                          chr(53)||
                          chr(56)||
                          chr(44)||
                          chr(84)||
                          chr(70)||
                          chr(48)||
                          chr(56)||
                          chr(48)||
                          chr(45)||
                          chr(51)||
                          chr(53)||
                          chr(53)||
                          chr(52)||
                          chr(45)||
                          chr(53)||
                          chr(49)||
                          chr(57)||
                          chr(53)||
                          chr(44)||
                          chr(75)||
                          chr(83)||
                          chr(48)||
                          chr(56)||
                          chr(48)||
                          chr(45)||
                          chr(50)||
                          chr(49)||
                          chr(53)||
                          chr(55)||
                          chr(45)||
                          chr(55)||
                          chr(52)||
                          chr(48)||
                          chr(56)||
                          chr(44)||
                          chr(10)||
                          chr(77)||
                          chr(89)||
                          chr(48)||
                          chr(57)||
                          chr(48)||
                          chr(55)||
                          chr(56)||
                          chr(51)||
                          chr(48)||
                          chr(50)||
                          chr(50)||
                          chr(54)||
                          chr(56)||
                          chr(44)||
                          chr(74)||
                          chr(72)||
                          chr(48)||
                          chr(56)||
                          chr(48)||
                          chr(56)||
                          chr(48)||
                          chr(51)||
                          chr(52)||
                          chr(52)||
                          chr(53)||
                          chr(49)||
                          chr(48)||
                          chr(44)||
                          chr(78)||
                          chr(77)||
                          chr(48)||
                          chr(56)||
                          chr(48)||
                          chr(50)||
                          chr(53)||
                          chr(55)||
                          chr(48)||
                          chr(53)||
                          chr(51)||
                          chr(53)||
                          chr(56)||
                          chr(44)||
                          chr(78)||
                          chr(75)||
                          chr(48)||
                          chr(56)||
                          chr(48)||
                          chr(49)||
                          chr(49)||
                          chr(49)||
                          chr(57)||
                          chr(48)||
                          chr(53)||
                          chr(54)||
                          chr(56)||
                          chr(41) str
                   from   dual)
select str,
       replace(replace(str, chr(10), ' {LF} '), chr(13), ' {CR} ') replaced_str,
       translate(str, chr(10)||chr(13), '  ') translated_str,
       case when dump(str) = 'Typ=1 Len=151: 77,79,66,73,76,69,32,80,72,79,78,69,32,66,73,76,76,10,40,73,76,76,67,32,32,76,73,78,79,32,48,56,48,45,51,50,52,50,45,49,52,48,52,44,32,75,75,32,48,56,48,45,57,49,57,56,45,51,51,53,56,44,84,70,48,56,48,45,51,53,53,52,45,53,49,57,53,44,75,83,48,56,48,45,50,49,53,55,45,55,52,48,56,44,10,77,89,48,57,48,55,56,51,48,50,50,54,56,44,74,72,48,56,48,56,48,51,52,52,53,49,48,44,78,77,48,56,48,50,53,55,48,53,51,53,56,44,78,75,48,56,48,49,49,49,57,48,53,54,56,41' then 'Y' else 'N' end matches_orig_dump
from   test_data;

STR                                                                                                       REPLACED_STR                                                                                                                                                                                             TRANSLATED_STR                                                                                                                                                                                           MATCHES_ORIG_DUMP
--------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------------
MOBILE PHONE BILL
(ILLC  LINO 080-3242-1404, KK 080-9198-3358,TF080-3554-5195,KS080-2157-7408,
MY09078302268,JH08080344510,NM08025705358,NK08011190568)                                                  MOBILE PHONE BILL {LF} (ILLC  LINO 080-3242-1404, KK 080-9198-3358,TF080-3554-5195,KS080-2157-7408, {LF} MY09078302268,JH08080344510,NM08025705358,NK08011190568)                                        MOBILE PHONE BILL (ILLC  LINO 080-3242-1404, KK 080-9198-3358,TF080-3554-5195,KS080-2157-7408, MY09078302268,JH08080344510,NM08025705358,NK08011190568)                                                  Y                
MY09078302268,JH08080344510,NM08025705358,NK08011190568)

i want to test this but i cant quite comprehend the syntax. Is str the field and is dual the table name? — hansolo, Sep 11 '15 at 14:04
I've just made up an inline view consisting of one column ("str") that's a literal value, and one row (the dual table only ever has one row), just to have some sample data to run the dump() function against to show you some results. It saves having to write out the string-literal twice (seeing as I pass it into both dump functions). In Oracle, you always have to select from a table, even if it's a literal value or a function call (eg. sysdate) that you're getting. I know other db platforms let you do "select 'some literal value'" but Oracle doesn't. That's where the DUAL table comes into play. — Boneist, Sep 11 '15 at 14:20
So, for your table and column(s) you'd do, for example: `select dump(your_col) from your_table` — Boneist, Sep 11 '15 at 14:23
thank you ... running currently ... seems to be taking a while. — hansolo, Sep 11 '15 at 14:30
I would suggest you filter it for those rows you know you've definitely got a problem with. No point in running it for everything when you'll only look at a sample set of rows, right? — Boneist, Sep 11 '15 at 14:40
got it finally. 10s are definitely the issue from what I can see. Very confused why the replace/translate isnt working Typ=1 Len=151: 77,79,66,73,76,69,32,80,72,79,78,69,32,66,73,76,76,10,40,73,76,76,67,32,32,76,73,78,79,32,48,56,48,45,51,50,52,50,45,49,52,48,52,44,32,75,75,32,48,56,48,45,57,49,57,56,45,51,51,53,56,44,84,70,48,56,48,45,51,53,53,52,45,53,49,57,53,44,75,83,48,56,48,45,50,49,53,55,45,55,52,48,56,44,10,77,89,48,57,48,55,56,51,48,50,50,54,56,44,74,72,48,56,48,56,48,51,52,52,53,49,48,44,78,77,48,56,48,50,53,55,48,53,51,53,56,44,78,75,48,56,48,49,49,49,57,48,53,54,56,41 — hansolo, Sep 11 '15 at 14:49
I've updated my answer with a test case that works for me, based on the dumped data you supplied. Can you run the same script on your db and see if it works? — Boneist, Sep 11 '15 at 15:23
ok, I've changed it to be a select statement mimicking the test_data table. You should be able to copy the statement (make sure you include the WITH clause!) and run it. — Boneist, Sep 11 '15 at 15:50
problem solved. the issue is that I had TWO fields I needed to apply the translate/replace to, not just one. I found out by processing every field from the query using the dump command you suggested and I started to see 10s and 13s in another field. Thank you VERY much for your patience and help. — hansolo, Sep 11 '15 at 15:58
No problem! Sometimes you can't see the wood for the trees; I'm just glad you managed to get it sorted *{:-) — Boneist, Sep 11 '15 at 16:00

Removing LF with Oracle

1 Answers1