Home » SQL & PL/SQL » SQL & PL/SQL » Regular Expression In Query Help Needed...
Regular Expression In Query Help Needed... [message #234376] Tue, 01 May 2007 05:13 Go to next message
rahul.priyadarshy
Messages: 28
Registered: December 2005
Junior Member
Hi ,

I have a requirement which is below.

Table Name:Test1

SQL> select * from test1;

NAME
-----------------------------------------------------------------
This pw0001
this is pw0002 is on
aaaaaaaaaaaaaa

This name column can contain string starting from pw and after that any number of length 1 to 9 like pw1,pw002,pw345678.

As its shown in the table columns also. We need to extract those values which have that pattern matching in the column value.

Like Output Should be:
pw0001
pw0002

My Query And Its Output:

1* select regexp_substr(name,'pw[[:digit:]]','1') from test1
SQL> /

REGEXP_SUBSTR(NAME,'PW[[:DIGIT:]]','1')
--------------------------------------------------------------------------------
pw0
pw0


Can anyone suggest me what to change in the pattern of the query.

Thanks In Advance

Regards
Rahul P
Re: Regular Expression In Query Help Needed... [message #234385 is a reply to message #234376] Tue, 01 May 2007 06:30 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You miss to indicate there are any number of digits:
SQL> select regexp_substr(col,'pw[[:digit:]]*','1') from t
  2  /
REGEXP_SUBSTR(COL,'PW[[:DIGIT:
------------------------------
pw0001
pw0002


3 rows selected.

Regards
Michel
Previous Topic: default(max) size of NUMBER datatype in oracle-10g
Next Topic: Index Name in User_Indexes with No Column Name
Goto Forum:
  


Current Time: Thu Dec 08 22:07:22 CST 2016

Total time taken to generate the page: 0.07948 seconds