从文本SQL中删除多余的+ [英] remove extra + from text SQL
问题描述
这是指以前别人问过的一个问题 上一个问题
this refers to a question asked by someone else previously previous question
我的问题是我如何适应该解决方案,以便在运行任何功能/脚本之前,将名称和值字段中的所有附加+剥离掉并进行更新,以致没有附加+残留.
my question is how do I adapt this solution so that before any function/script is ran the name and value fields are stripped of any additional + and updated so no additional + remain.
例如
Name Value
A+B+C+ 1+2+3+
A++B 1++2
这应该更新为
Name Value
A+B+C 1+2+3
A+B 1+2
一旦完成此更新,我就可以运行上一个问题中提供的解决方案.
once this update has taken place, I can run the solution provided in the previous question.
谢谢
推荐答案
您需要将++
替换为+
,并删除字符串末尾的+
.
You need to replace ++
with +
and to remove the +
at the end of the string.
/* sample data */
with input(Name, Value) as (
select 'A+B+C+' ,'1+2+3+' from dual union all
select 'A++B' ,'1++2' from dual
)
/* query */
select trim('+' from regexp_replace(name, '\+{2,}', '+') ) as name,
trim('+' from regexp_replace(value, '\+{2,}', '+') ) as value
from input
如果您需要更新表,则可能需要:
If you need to update a table, you may need:
update yourTable
set name = trim('+' from regexp_replace(name, '\+{2,}', '+') ),
value= trim('+' from regexp_replace(value, '\+{2,}', '+') )
以更紧凑的方式,无需外部装饰(假设您没有前导+
):
In a more compact way, without the external trim ( assuming you have no leading +
):
/* sample data */
with input(Name, Value) as (
select 'A+B+C+' ,'1+2+3+' from dual union all
select 'A++B+++C+' ,'1++2+++3+' from dual union all
select 'A+B' ,'1+2' from dual
)
/* query */
select regexp_replace(name, '(\+)+(\+|$)', '\2') as name,
regexp_replace(value, '(\+)+(\+|$)', '\2') as value
from input
这篇关于从文本SQL中删除多余的+的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!