用Oracle删除LF
用Oracle删除LF
提问于 2015-09-11 13:10:36
问题：我在描述字段中有LF中断，当我发送到子系统上传时会引起问题。
研究：在浏览了这个网站和其他网站之后，我发现了很多建议，但到目前为止，它们都没有奏效。
Oracle REPLACE() function isn't handling carriage-returns & line-feeds
下面是我目前查询的内容，但是LFs似乎是一个问题，因为它们没有被替换。
替换(替换(字段，chr(10)，'')，chr(13)，'')
我是个新手，所以我肯定我忽略了一些简单的东西。任何帮助都是非常感谢的。
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'))回答 1
Stack Overflow用户
回答已采纳
发布于 2015-09-11 13:34:58
对我来说很管用。也许您应该使用dump()函数来计算文本字段的内容，看看文本中是否有10或13的内容？
例如：
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好的，在下面的注释中提供了来自转储的数据：
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)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/32524340
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号