从文本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屋！