oracle 从字符串中删除特定单词

remove specific word from string

提问by Bhushan

I am using oracle10g.

我正在使用oracle10g.

I want to remove all occurrences of particular word from sentence, But I don't want to remove any other word which contains other characters between a-z or A-Z.

我想从句子中删除所有出现的特定单词，但我不想删除包含 az 或 AZ 之间其他字符的任何其他单词。

For example, Following is a sentence from which I want to remove some:

例如，以下是我要删除的句子some：

some text, 123 someone, another text some1

Expected output:

预期输出：

 text, 123 someone, another text

Note that I also want to remove someword if it contains some+ any other word than A-Zand a-zbefore or after some.

请注意，我也想删除some的字，如果它包含some+其他词不是A-Z和a-z之前或之后some。

This is what I have tried so far:

这是我迄今为止尝试过的：

select replace('some text, 123 someone, another text some1','some','') 
from dual;

I am getting output:

我得到输出：

 text, 123 one, another text 1

In above output I am expecting someonenot to be replaced and some1should be replaced totally.

在上面的输出中，我预计someone不会被替换，而some1应该被完全替换。

How should I achieve this? Any suggestion will be appreciated.

我应该如何实现这一目标？任何建议将不胜感激。

Edit:For clarity this is another example of what I am looking for:

编辑：为清楚起见，这是我正在寻找的另一个例子：

some other text someone other text, someB some1 some.

output should be:

输出应该是：

 other text someone other text, someB 

From above sentence someBis not removed because it has characters between a-z
And some1and some.is removed becasue it doesn't has characters between a-z.

从上面这句话someB是不是删除，因为它具有之间的字符a-z
并some1和some.被删除becasue它不具有字符之间a-z。

Edit2

编辑2

If I use regex:

如果我使用正则表达式：

select REGEXP_REPLACE('some text, 123 someone, another text some1','[^a-zA-Z]','')
from dual

I am getting output:

我得到输出：

sometextsomeoneanothertextsome

Expected output:

预期输出：

sometextsomeoneanothertext

Note that I want some1also be removed from string as it contains other character than A-Z.

请注意，我some1也想从字符串中删除，因为它包含除A-Z.

Answers using regexare also appreciated.

使用regex的答案也受到赞赏。

回答by ThinkJet

Due to lack of support for lookbehind/lookaheadand word boundary(\b)in Oracle implementation of regular expression, it seems to be impossible to meet all requirements in single REGEXP_REPLACEcall. Especially for case, pointed out by Egor Skriptunoff: pattern matches, followed one by one with only one separator between them like some some some some ....

由于Oracle正则表达式实现中缺乏对lookbehind/lookahead和wordboundary ( \b)的支持，单次REGEXP_REPLACE调用似乎不可能满足所有要求。特别是对于Egor Skriptunoff 指出的情况：模式匹配，然后一一匹配，它们之间只有一个分隔符，例如some some some some ....

Without this case it's possible to match all such strings with this call:

如果没有这种情况，则可以通过此调用匹配所有此类字符串：

regexp_replace(
  source_string,                                       -- source string
  '([^[:alnum:]]|^)((\d)*some(\d)*)([^[:alnum:]]|$)',  -- pattern
  '',                                              -- leave separators in place
  1,                                                   -- start from beginning
  0,                                                   -- replace all occurences
  'im'                                                 -- case-insensitive and multiline 
);

Pattern parts:

图案部分：

(                -- start of Group #1
  [^[:alnum:]]   -- any non-alphanumeric character 
  |              -- or 
  ^              -- start of string or start of line 
)                -- end of Group #1
(                -- start of Group #2
  (              -- start of Group #3 
    \d           -- any digit
  )              -- end of Group #3
  *              -- include in previous group zero or more consecutive digits
  some           -- core string to match
  (              -- start of group #4
    \d           -- any digit
  )              -- end of group #4  
  *              -- include in previous group zero or more consecutive digits
)                -- end of Group #2
(                -- start of Group #5
  [^[:alnum:]]   -- any non-alphanumeric character 
  |              -- or
  $              -- end of string or end of line
)                -- end of Group #5

Because separators used for matching (Group #1 and Group #5) included in match pattern it will be removed from source string on successful match, so we need restore this parts by specifying in third regexp_replaceparameter.

因为匹配模式中包含的用于匹配的分隔符（Group #1 和 Group #5）在成功匹配时将从源字符串中删除，所以我们需要通过在第三个regexp_replace参数中指定来恢复这部分。

Based on this solution it's possible to replace all, even repetitive occurrences within a loop.

基于此解决方案，可以替换循环中的所有甚至重复出现的事件。

For example, you can define a function like that:

例如，您可以定义这样的函数：

create or replace function delete_str_with_digits(
  pSourceString in varchar2, 
  pReplacePart  in varchar2  -- base string (like 'some' in question)
)
  return varchar2
is
  C_PATTERN_START constant varchar2(100) := '([^[:alnum:]]|^)((\d)*';
  C_PATTERN_END   constant varchar2(100) := '(\d)*)([^[:alnum:]]|$)';

  vPattern         varchar2(4000);
  vCurValue        varchar2(4000);
  vPatternPosition binary_integer;
begin

  vPattern := C_PATTERN_START || pReplacePart || C_PATTERN_END;
  vCurValue := pSourceString;

  vPatternPosition := regexp_instr(vCurValue, vPattern);

  while(vPatternPosition > 0) loop
    vCurValue := regexp_replace(vCurValue, vPattern,'',1,0,'im');
    vPatternPosition := regexp_instr(vCurValue, vPattern);
  end loop;

  return vCurValue;  

end;

and use it with SQL or other PL/SQL code:

并将其与 SQL 或其他 PL/SQL 代码一起使用：

SELECT 
  delete_str_with_digits(
    'some text, -> awesome <- 123 someone, 3some3
     line of 7 :> some some some some some some some <
222some  another some1? some22 text 0some000', 
    'some'
  )  as result_string
FROM 
  dual

SQLFiddle example

SQLFiddle example

回答by Gordon Linoff

Here is an approach that doesn't use regular expressions:

这是一种不使用正则表达式的方法：

select trim(replace(' '||'some text, 123 someone, another text some1'||' ',
                    ' some ',' '
                   ) 
           )
from dual;

回答by Robert

You can use REGEXP_REPLACEfunction as below

您可以使用REGEXP_REPLACE如下功能

SELECT REGEXP_REPLACE('some text, 123 someone, another text some1', '(^|\s)some(^|\s)', '')
FROM dual;

SQL Fiddle Demo

SQL 小提琴演示

• REGEXP_REPLACE

• REGEXP_REPLACE

回答by Amit Joki

Use the REGEX_REPLACE()as this

REGEX_REPLACE()像这样使用

SELECT REGEXP_REPLACE('some other text someone other text, someB some1 some.', '(some\s|some\d|some[.])','')
FROM dual;

SQL Fiddle Demo

SQL 小提琴演示

Hope this helps. Mark it as answer if it helps :)

希望这可以帮助。如果有帮助，请将其标记为答案:)

If you want any other characters other than .then just add it to the last []like if you also want to match some#, then just add it to .like this [.#]

如果您想要除此之外的任何其他字符，.只需将其添加到最后，[]就像您还想匹配某个#，那么只需将其添加为.这样[.#]

回答by AnBisw

Something like this

像这样的东西

SELECT REGEXP_REPLACE('some text, 123 someone, another text some1 some@, SOMEone SoME1', 
                      '(some\d|some[^[:alnum:]]|some$)','',1,0,'i')
FROM dual;

Output:

输出：

text, 123 someone, another text , SOMEone

Here's the explanation of the pattern and the options:

这是模式和选项的解释：

1. some\d- The word "some" followed by any digit.
2. some[^[:alnum:]]- The word "some" followed by any non alpha numeric character. The ^stands for negation and [:alnum:]for alphanumerics. So basically, [^[:alnum:]]is the opposite of [[:alnum:]].
3. some$if the string ends with the word "some".
4. 1,0,'i'- Start from the first occurrence 1and followed by all occurrences 0and case insensitive i.

1. some\d- 单词“some”后跟任何数字。
2. some[^[:alnum:]]- 单词“some”后跟任何非字母数字字符。该^代表否定和[:alnum:]对字母数字。所以基本上，[^[:alnum:]]是相反的[[:alnum:]]。
3. some$如果字符串以单词“some”结尾。
4. 1,0,'i'- 从第一次出现开始1，然后是所有出现0并且不区分大小写i。