Home » SQL & PL/SQL » SQL & PL/SQL » Regular Expression
Regular Expression [message #203495] Wed, 15 November 2006 05:19 Go to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
I have a string and I want to confirm that it has only at the most 4 words in it. How can I use regular expression for this??

thanks in advance.
Re: Regular Expression [message #203508 is a reply to message #203495] Wed, 15 November 2006 06:08 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I guess it may come down to what really constitutes a "word", but you can consider the following:

SQL> select length(regexp_replace('Count the number of words','[^[:space:]]'))+1 "# of Words" from dual;

# of Words
----------
         5
Re: Regular Expression [message #203510 is a reply to message #203508] Wed, 15 November 2006 06:17 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
thanks but how about if the string has consecutive spaces like 'Count the number of words'??
Re: Regular Expression [message #203520 is a reply to message #203510] Wed, 15 November 2006 06:43 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
select length(regexp_replace('Count             the  number of   words','[^ ]|( ){2,}','\1'))+1 "# of Words" from dual;
icon7.gif  Re: Regular Expression [message #203522 is a reply to message #203508] Wed, 15 November 2006 06:52 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
thanks! that was helpful.. Now, (if you don't mind) my next requirement is to extract those words Smile. Thus, it should return:
Count
the
number
of
words


Thanks in advance..
Re: Regular Expression [message #203534 is a reply to message #203522] Wed, 15 November 2006 07:47 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
When you say extract, do you mean you are looking to place each individual word into a separate row of a table or just produce a multiple line output as you have shown ?
Re: Regular Expression [message #203539 is a reply to message #203534] Wed, 15 November 2006 08:06 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Multiple rows.. is that possible??
Re: Regular Expression [message #203546 is a reply to message #203539] Wed, 15 November 2006 08:28 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> select rownum, regexp_substr(str, '[^ ]+', 1, level) Words
  2 	from (select 'Count             the  number of   words' str from dual)
  3  connect by regexp_substr(str, '[^ ]+', 1, level) is not null;

ROWNUM	WORDS
------	------
     1	Count
     2	the
     3	number
     4	of
     5	words
Re: Regular Expression [message #203547 is a reply to message #203546] Wed, 15 November 2006 08:33 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Brilliant!! Thumbs Up
Re: Regular Expression [message #203739 is a reply to message #203547] Thu, 16 November 2006 03:04 Go to previous messageGo to next message
anithalakshmi
Messages: 9
Registered: August 2006
Junior Member
Hi,

I am a junior member of the forum can u please explain the regexp_replace function used in the following query.

select length(regexp_replace('Count the number of words','[^[:space:]]'))+1 "# of Words" from dual;

Thanks in advance,
Anitha.
Re: Regular Expression [message #203774 is a reply to message #203739] Thu, 16 November 2006 05:03 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
This particular regex is instructing the expression to replace every NON-space character with nothing, essentially remove everything EXCEPT the whitespaces. The [^[:space:]] can also be written as [^ ] as indicated in the follow-up posts.
Previous Topic: export `create table`, 'insert into`...
Next Topic: Help in Join
Goto Forum:
  


Current Time: Tue Dec 06 02:31:46 CST 2016

Total time taken to generate the page: 0.08525 seconds