Oracle REPLACE() function isn't handling carriage-returns & line-feeds
Another way is to use TRANSLATE:
TRANSLATE (col_name, 'x'||CHR(10)||CHR(13), 'x')
The 'x' is any character that you don't want translated to null, because TRANSLATE doesn't work right if the 3rd parameter is null.
Removing LF with Oracle
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)
Carriage Return isn't being removed from exported .csv in Access
Try this using two steps:
Replace(Replace([field name], Chr(13), ""), Chr(10), "")
Oracle SQL: force carriage return / new line in output text file
The reason why you're getting chr(10) as text is because, well, you're including it in the text string. You have to concatenate it into the text string instead, e.g. 'some text'||chr(10)||'some more text'
However, I think listagg is not really what you should be using here, especially since there's a limit on how much you can output in the result string.
Instead, I think you're after an UNPIVOT, something like:
WITH sample_data AS (SELECT 1 shipper_id, 10 item_number, 'abc' article, 100 quantity FROM dual UNION ALL
SELECT 2 shipper_id, 20 item_number, 'efg' article, 200 quantity FROM dual UNION ALL
SELECT 3 shipper_id, 30 item_number, 'hij' article, 300 quantity FROM dual)
-- end of mimicking a table called sample_data with data in it
-- see SQL below:
SELECT CASE WHEN row_number() OVER (PARTITION BY shid
ORDER BY CASE WHEN column_name = 'Item No.:' THEN 1
WHEN column_name = 'Article:' THEN 2
WHEN column_name = 'Quantity:' THEN 3
ELSE 4
END) = 1
THEN shid
END shipper_id,
column_name||' '||vals results
FROM (SELECT shipper_id shid,
to_char(item_number) "Item No.:",
article "Article:",
to_char(quantity) "Quantity:"
FROM sample_data)
UNPIVOT (vals FOR column_name IN ("Item No.:", "Article:", "Quantity:"))
ORDER BY shid,
CASE WHEN column_name = 'Item No.:' THEN 1
WHEN column_name = 'Article:' THEN 2
WHEN column_name = 'Quantity:' THEN 3
ELSE 4
END;
SHIPPER_ID RESULTS
---------- --------------------------------------------------
1 Item No.: 10
Article: abc
Quantity: 100
2 Item No.: 20
Article: efg
Quantity: 200
3 Item No.: 30
Article: hij
Quantity: 300
The row_number() analytic function is being used here to ensure the shipper_id is displayed for the first row only.
The case when column_name = ... case expression is used to ensure the columns are output in the correct order (item_number, article and quantity) (both within the row_number() analytic function and in the overall results).
ETA: If you could have multiple item_numbers per shipper id, then a couple of tweaks need to be made to the above query:
WITH sample_data AS (SELECT 1 shipper_id, 10 item_number, 'abc' article, 100 quantity FROM dual UNION ALL
SELECT 1 shipper_id, 11 item_number, 'xyz' article, 110 quantity FROM dual UNION ALL
SELECT 2 shipper_id, 20 item_number, 'efg' article, 200 quantity FROM dual UNION ALL
SELECT 3 shipper_id, 30 item_number, 'hij' article, 300 quantity FROM dual)
-- end of mimicking a table called sample_data with data in it
-- see SQL below:
SELECT CASE WHEN row_number() OVER (PARTITION BY shid ORDER BY CASE WHEN column_name = 'Item No.:' THEN 1 WHEN column_name = 'Article:' THEN 2 WHEN column_name = 'Quantity:' THEN 3 ELSE 4 END) = 1
THEN shid
END shipper_id,
column_name||' '||vals results
FROM (SELECT shipper_id shid,
item_number,
to_char(item_number) "Item No.:",
article "Article:",
to_char(quantity) "Quantity:"
FROM sample_data)
UNPIVOT (vals FOR column_name IN ("Item No.:", "Article:", "Quantity:"))
ORDER BY shid,
item_number,
CASE WHEN column_name = 'Item No.:' THEN 1 WHEN column_name = 'Article:' THEN 2 WHEN column_name = 'Quantity:' THEN 3 ELSE 4 END;
SHIPPER_ID RESULTS
---------- --------------------------------------------------
1 Item No.: 10
Article: abc
Quantity: 100
Item No.: 11
Article: xyz
Quantity: 110
2 Item No.: 20
Article: efg
Quantity: 200
3 Item No.: 30
Article: hij
Quantity: 300
How to remove everything in a field after an newline or carriage return
Consider:
UPDATE my_table
SET my_column = REGEXP_REPLACE(my_column , chr(13) || '.*$', '', 1, 0, 'n')
WHERE ACCOUNT = 123456
Rationale :
chr(13) || '.*'matches from the (first) new line to the end of string (greadily)- the matching part of the string will be replaced by the empty string
- the final
'n'option allows the dot character to match on newlines (by default it doesn't)
Demo on DB Fiddle:
WITH a AS (SELECT 'John Smith' || chr(13) || 'John Smith' txt FROM DUAL)
SELECT
a.txt,
REGEXP_REPLACE(a.txt, chr(13) || '.*', '', 1, 0, 'n') new_txt
FROM a;
TXT | NEW_TXT
:-------------------- | :---------
John Smith | John Smith
John Smith |
Note: as commented by @Alex Poole, if you want to match on both new line and carriage return, you can use :
'('||chr(13)||'|'||chr(10)||').*'
Related Topics
SQL Server Stored Procedure Parameters
Split Varchar into Separate Columns in Oracle
How to Exclude Records with Certain Values in SQL Select
Sql: Last_Value() Returns Wrong Result (But First_Value() Works Fine)
Guid Primary /Foreign Key Dilemma SQL Server
How to Compute Tf/Idf with SQL (Bigquery)
How to Swap Column Values in SQL Server 2008
Combining Order by and Union in SQL Server
What Is Best Way to Get Last Indexof Character in SQL 2008
Asynchronous Triggers in SQL Server 2005/2008
Do Link Tables Need a Meaningless Primary Key Field
How to Fill Missing Dates by Groups in a Table in SQL
Similarity Function in Postgres with Pg_Trgm
How to Keep Only One Row of a Table, Removing Duplicate Rows
Finding Simultaneous Events in a Database Between Times