CODEWITHSUNDEEP
regexoracle-database
Code Grasshopper
Code Grasshopper

Reputation: 620

Delete spaces, commas, colon, semicolon with regexp

I am trying to print the number of words in the following txt ignoring the special characters like =,;:'. the function already works, but all the examples I have found here are not working properly, this is what I've tried.

The txt is:

select name from v$database; select serial# from db4sql; clear 

Select (length (txt) - length(regexp_replace(txt, '[0-9\. ,]+$',''))

Upvotes: 0

Views: 863

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

I am trying to print the number of words in the following txt ignoring the special characters like =,;:'.

Any would you make it so complex to ignore punctuations when all you need is to count the number of words. It is a simple query using REGEXP_COUNT and [[:blank:]] character class.

SQL> WITH DATA AS(
  2  SELECT q'[select name from v$database; select serial# from db4sql; clear]' txt
  3  FROM dual
  4  )
  5  SELECT REGEXP_COUNT(txt, '[^[:blank:]]+') cnt
  6  FROM DATA
  7  /

       CNT
----------
         9

SQL>

Delete spaces, comas, colon, semicolon with regexp

Ok. If you just want to replace the punctuations and return only pure alphabets, then use the [[:punct:]] character class.

SQL> WITH DATA AS(
  2  SELECT q'[select name from v$database; select serial# from db4sql; clear]' txt
  3  FROM dual
  4  )
  5  SELECT REGEXP_replace(txt, '[[:punct:]]') cnt
  6  FROM DATA
  7  /

CNT
----------------------------------------------------------
select name from vdatabase select serial from db4sql clear

SQL>

Upvotes: 1

anudeepks
anudeepks

Reputation: 1132

The select statement will always give you the length not the number of words in the string, i have modified the query . Please see if this suits your needs,

SQL>  declare
  2   txt varchar2(1000):='select name from v$database; select serial# from db4sql; clear ';
  3   newval varchar2(100);
  4   begin
  5   select  REGEXP_COUNT( txt, '[^ ]+' )into newval from dual;
  6   dbms_output.put_line('the total number of words are '||newval);
  7   end;
  8
  9   /
the total number of words are 9

PL/SQL procedure successfully completed.

Upvotes: 1

Related Questions