首页 » ORACLE » oralce 去除数据库中的回车换行符

oralce 去除数据库中的回车换行符

数据库中有时会存在一种不可见字符如回车换行符,一眼看不出来,但是又影响应用程序的显示,怎么样去除这些字符?

实验开始

web应用显示 后面多出很多空行如下

找到这条记录,去库里查看一下那个字段的值dump一下如下

sql>SELECT DUMP (score_activity_content)
FROM icme_noproject_score
WHERE  score_id = 109085841

———————————–

Typ=1 Len=52: 189,161,191,181,189,204,211,253,212,218,193,217,180,178,187,164,192,237,214,208,181,196,202,181,202,169

,13,10,45,45,214,208,185,250,201,231,199,248,210,189,202,166,13,10,13,10,13,10,13,10,13,10

不难看出有回车换行符,像常用的这几个不说我觉的应该也记住,去网上下个ascII表放显示器上

当然也可以用instr函数具体用法google,举个例子

SELECT *
FROM icme_noproject_score
WHERE ic_code = ‘3904010RV’ AND INSTR (score_activity_content, CHR (13))<>0

知道了是哪些字符在做怪,那就好办了,replace吧

UPDATE icme_noproject_score
SET score_activity_content = replace(score_activity_content,chr(13))
WHERE score_id = 109085841;
UPDATE icme_noproject_score
SET score_activity_content = replace(score_activity_content,chr(10))
WHERE score_id = 109085841;

commit;

再去web应用查一下,看是不是美观多了

打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Ryann Rehm | #1
    2011-12-21 at 07:46

    In the Netherlands people take pride in cleaning the streets and sidewalks outside of their home. I dont see anything wrong with Hatchs comments. Most Americans these days are lazy and expect others to do their bidding for them.