Home » SQL & PL/SQL » SQL & PL/SQL » REGEXP_REPLACE: How to replace a list of words to null (Oracle 10g)
REGEXP_REPLACE: How to replace a list of words to null [message #303018] Wed, 27 February 2008 10:52 Go to next message
nallurioracle
Messages: 1
Registered: February 2008
Junior Member
Hi,

I need to replace if any word (from the list of words) is occoured in the given string, to null.

I have tried the following example:

WITH x AS
(SELECT 'air the quick brown fox jumps command of and king to if all' AS str
FROM dual)
SELECT str
, REGEXP_REPLACE (str, '((^| )(and|the|if|but|for|to|is|how|or|i|a|it|of)[^ ]*)') AS mod_str
FROM x

The output of mod_str is 'quick brown fox jumps command king'.
The words 'air' and 'all' are missing in the output.
Why?

Thanks in advance.
Re: REGEXP_REPLACE: How to replace a list of words to null [message #303033 is a reply to message #303018] Wed, 27 February 2008 12:19 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Because you have told it to replace any word that starts with any of the words in the pipe delimited list with a null.
Try adding a space after each 'word'
Re: REGEXP_REPLACE: How to replace a list of words to null [message #303065 is a reply to message #303018] Wed, 27 February 2008 20:13 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Here's an option:

SQL> WITH x AS
  2    (SELECT 'air the quick brown fox jumps command of and king to if all' AS str
  3    FROM dual)
  4  SELECT str
  5     , replace(REGEXP_REPLACE (replace(str, ' ', '# '),
  6      '(^| )(and|the|if|but|for|to|is|how|or|i|a|it|of)(#|$)'),'#') AS mod_str
  7  FROM x;

STR
-----------------------------------------------------------
MOD_STR
--------------------------------------------------
air the quick brown fox jumps command of and king to if all
air quick brown fox jumps command king all
Re: REGEXP_REPLACE: How to replace a list of words to null [message #303770 is a reply to message #303018] Sun, 02 March 2008 13:25 Go to previous messageGo to next message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
the same as on OTN thread.
Re: REGEXP_REPLACE: How to replace a list of words to null [message #304037 is a reply to message #303018] Mon, 03 March 2008 16:44 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
You could just create a function to do it.
Ive created one that recursively calls REPLACE.

Dont always beleive the mantra applied to "SQL is better than PLSQL". This solution is simple, easy to maintain, and scalable.

Basically, put a space at each end of the string, and then replace the list of words with a space. Then trim the result.

If you chose this route, I would suggest you enhance the function to read data from a table into a package array, and check if its empty on each call. If its empty, read the table. That way, it will be "cached" on subsequent calls.

This gives you the advantage of adding extra replacement words without changing any code (all of the previous solutions require changes to code to achieve this unless you are thinking down the dynamic sql route).

create or replace function replace_list(in_string in varchar2)
  return varchar2 is
  type tt_list is table of varchar2(4000) index by binary_integer;
  t_list   tt_list;
  v_string varchar2(4000);
begin
  t_list(1) := ' and '; -- Put space either side.
  t_list(2) := ' the ';
  t_list(3) := ' if ';
  t_list(4) := ' but ';
  t_list(5) := ' for ';
  t_list(6) := ' to ';
  t_list(7) := ' is ';
  t_list(8) := ' how ';
  t_list(9) := ' or ';
  t_list(10) := ' i ';
  t_list(11) := ' a ';
  t_list(12) := ' it ';
  t_list(13) := ' of ';

  -- Add Space to either end of string
  v_string := ' ' || in_string || ' ';

  -- Recursively call replace function with items in list
  for i in t_list.first .. t_list.last loop
    v_string := replace(v_string, t_list(i), ' ');
  end loop;

  return trim(v_string);

end;


Then run the following to test your strings:-

SELECT 'air the quick brown fox jumps command of and king to if all'
       ,replace_list('air the quick brown fox jumps command of and king to if all')
FROM dual;
Re: REGEXP_REPLACE: How to replace a list of words to null [message #304046 is a reply to message #304037] Mon, 03 March 2008 19:57 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
PL/SQL does indeed have it's place. However, may need to change the function to accommodate punctuations at the end of the sentence.

SQL> SELECT replace_list('air the quick brown fox jumps command of and king to if all for!')
  2  FROM dual;

REPLACE_LIST('AIRTHEQUICKBROWNFOXJUMPSCOMMANDOFANDKINGTOIFALLFOR!')
--------------------------------------------------------------------------------
air quick brown fox jumps command king all for!

SQL version to accommodate punctuation:
SQL> WITH x AS
  2      (SELECT 'air the quick brown fox jumps command of and king to if all for!' AS str
  3      FROM dual)
  4    SELECT replace(REGEXP_REPLACE (replace(str, ' ', '# '),
  5        '(^| )(and|the|if|but|for|to|is|how|or|i|a|it|of)((\W)|(#|$))','\4'),'#') AS mod_str
  6  FROM x;

MOD_STR
-------------------------------------------
air quick brown fox jumps command king all!
Previous Topic: unable to use dbms_lob.append or writeappend
Next Topic: consuming web service using utl_dbws
Goto Forum:
  


Current Time: Fri Dec 09 19:39:26 CST 2016

Total time taken to generate the page: 0.10059 seconds