remove specific word from string

Question

I am using 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.

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

some text, 123 someone, another text some1

Expected output:

 text, 123 someone, another text

Note that I also want to remove some word if it contains some+ any other word than A-Z and a-z before or after 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 someone not to be replaced and some1 should be replaced totally.

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 someB is not removed because it has characters between a-z
And some1 and some. is removed becasue it doesn't has characters between a-z.

Edit2

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 some1 also be removed from string as it contains other character than A-Z.

Answers using regex are also appreciated.

Do you have to handle "real" sequences as well? E.g. sequences where 'some' is the first word or the last word in the sentence and might therefore be preceded by nothing / succeeded by a full stop '.' etc. ? — Frank Schmitt
– Frank Schmitt, Commented Feb 3, 2014 at 12:29
@FrankSchmitt I want to remove every some from sentence, No need to handle sequence. Every some which doesn't contains some +words between A-Z and a-z at the beginning and at the end. — Bhushan
– Bhushan, Commented Feb 3, 2014 at 12:39

ThinkJet · Accepted Answer · 2014-02-06 09:25:47Z

Due to lack of support for lookbehind/lookahead and word boundary(\b) in Oracle implementation of regular expression, it seems to be impossible to meet all requirements in single REGEXP_REPLACE call. 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 ....

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
  '\1\5',                                              -- 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_replace parameter.

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\5',1,0,'im');
    vPatternPosition := regexp_instr(vCurValue, vPattern);
  end loop;

  return vCurValue;  

end;

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

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

Gordon Linoff · Accepted Answer · 2014-02-03 12:21:59Z

3

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

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

answered Feb 3, 2014 at 12:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Bhushan Over a year ago

Thanks for the answer(and +1), Adding spaces before and after the sentence for first and last word solves the problem partially, I have already tried that. But I also want to remove words like Some1 or Some, or 1some which contains words other than A-Z or a-z.

Robert · Accepted Answer · 2014-02-03 12:29:16Z

1

You can use REGEXP_REPLACE function as below

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

SQL Fiddle Demo

REGEXP_REPLACE

answered Feb 3, 2014 at 12:29

Robert

25.8k8 gold badges70 silver badges86 bronze badges

3 Comments

Bhushan Over a year ago

Thanks for the answer(and +1), This removes some from the sentence, But I also want to remove words like Some1 or Some, or 1some which contains words other than A-Z or a-z.

Egor Skriptunoff Over a year ago

What about some some string?

Bhushan Over a year ago

@EgorSkriptunoff Both should be replaced in that case.

Amit Joki · Accepted Answer · 2014-02-05 14:23:09Z

1

Use the REGEX_REPLACE() as this

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

SQL Fiddle Demo

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 [.#]

answered Feb 5, 2014 at 14:23

Amit Joki

59.4k7 gold badges80 silver badges96 bronze badges

Comments

Anjan Biswas · Accepted Answer · 2014-02-06 04:42:04Z

1

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:

some\d - The word "some" followed by any digit.
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:]].
some$ if the string ends with the word "some".
1,0,'i' - Start from the first occurrence 1 and followed by all occurrences 0 and case insensitive i.

edited Feb 6, 2014 at 4:42

answered Feb 6, 2014 at 4:09

Anjan Biswas

8,0126 gold badges51 silver badges78 bronze badges

3 Comments

ThinkJet Over a year ago

What about beginning of the word, e.g. 'awesome' and more than one digit at the end of the word? sqlfiddle illustration

Bhushan Over a year ago

@ThinkJet Thanks for note, Yes the word awesome should not be replaces, and some123 should be replaced completely. Let me know if you have any solution or suggestion.

Anjan Biswas Over a year ago

@ThinkJet well, it was just an idea on how to go about achieving what the OP wanted. There could be 50 more scenarios like this, which the OP has to figure out and incorporate in the code.

Collectives™ on Stack Overflow

remove specific word from string

5 Answers 5

Comments

1 Comment

3 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

3 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related