Home » SQL & PL/SQL » SQL & PL/SQL » regex_like number, *, - (oracle, 10g, windows 2003 server)
regex_like number, *, - [message #420847] Wed, 02 September 2009 10:46 Go to next message
kang
Messages: 89
Registered: November 2007
Member
with data as(
select '1234561234567' col1, 1 no from dual union
select '123456*******', 2 from dual union
select '123456***4567', 3 from dual union
select '123456-1234567', 4 from dual union
select '123456A234567', 5 from dual
) select * from data
where REGEXP_LIKE(col1, '[0-9\\-*]*')


how to filter the fifth row(123456A234567,5) out?
the col1 should be consist of number, *(asterisk), -(minus sign)
with any number any order.

Thanks.
Re: regex_like number, *, - [message #420851 is a reply to message #420847] Wed, 02 September 2009 11:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as(
  2  select '1234561234567' col1, 1 no from dual union
  3  select '123456*******', 2 from dual union
  4  select '123456***4567', 3 from dual union
  5  select '123456-1234567', 4 from dual union
  6  select '123456A234567', 5 from dual
  7  ) select * from data
  8  where REGEXP_LIKE(col1, '^[[:digit:]-*]+$')
  9  /
COL1                   NO
-------------- ----------
123456*******           2
123456***4567           3
123456-1234567          4
1234561234567           1

4 rows selected.

Regards
Michel
Re: regex_like number, *, - [message #420855 is a reply to message #420847] Wed, 02 September 2009 11:25 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
If the whole string must match, use starting (^) and end ($) position in string in REGEXP pattern. Then it will match any position of '*' and '-' across the string (see modified data).
As I did not succeed with '-' inside the pattern expression ([]), I placed it to its end.
SQL> with data as(
  2  select '1234561234567' col1, 1 no from dual union
  3  select '123456**--***', 2 from dual union
  4  select '123456***4-67', 3 from dual union
  5  select '123456-12**67', 4 from dual union
  6  select '123456A234567', 5 from dual
  7  ) select * from data
  8  where REGEXP_LIKE(col1, '^[0-9*-]*$');

COL1                  NO
------------- ----------
123456***4-67          3
123456**--***          2
123456-12**67          4
1234561234567          1

4 rows selected.

SQL> 
Previous Topic: How To Check The Index Creation Date(2 merged)
Next Topic: How to access Subversion (SVN) from PL/SQL
Goto Forum:
  


Current Time: Wed Dec 07 23:59:15 CST 2016

Total time taken to generate the page: 0.05946 seconds