如何在Postgres中删除字符串中的"\t"、"\n "或额外的空格?

我在Postgres中有一个表,表的列数如下。

col1
     The study was terminated early by the sponsor on 13 January 2014 due to a decision to modify     the drug development plan.   
     Due to positive preliminary results from other palifermin studies.   
     Asset terminated by PIB   
     Inconsistent training status of sniffer dogs   
     This study was terminated early due to poor recruitment   
     The study was terminated due to lack of recruitment.   
     The scientific director decided to terminate: low priority study with slow accrual   
     See Termination Reason in Detailed Description.   
     Investigator moved to new institution   
     This study was terminated for administrative reasons   
     The app was not completed in time to conduct a clinical trial on it within the funding grant's     award period 

字符串中有前导空格和后导空格,中间有’/n’或’/t’。我尝试了以下的查询,但似乎没有任何效果。

select btrim(col1, '\s') from table;

update table
SET col1 = upper(substring(REGEXP_REPLACE(col1, '(\s+)', '') from 1 for 1)) || lower(substring(REGEXP_REPLACE(why_stopped, '(\s+)', '') from 2));

update table
set col1= regexp_replace(col1, E'[\\n\\r\\f\\u000B\\u0085\\u2028\\u2029]+', ' ', 'g' );

select distinct replace( replace( replace( col1, E'\n', '\n' ), E'\t', '\t' ), E'\r', '\r' )
from table;

任何建议都将是非常有用的。

解决方案:

要在字符串中使用反斜杠转义,你必须在它们前面加一个 E;见 文件.

那就试试吧

btrim(col1, E' \t\n')

给TA打赏
共{{data.count}}人
人已打赏
解决方案

如何防止Doctrine覆盖生成值的列?

2022-5-12 3:00:07

解决方案

javascript查询选择器和const问题

2022-5-12 3:00:15

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索