• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

Oracle POSIX regular expressions.

Col1            Col1
tableA            tableB
VARCHAR            NUMBER
1            1
2            2
2a
2;
2?


Query Failure as expected
select *
from taba a, tabb b
where a.col1 = b.col1;

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    
*Action:


Following gives me all rows including 1 & 2 which are digits, why?
select * from taba
where regexp_like(col1, '[[:alnum:]]');

Following gives me all rows including 1 & 2 which are digits, why?
select * from taba
where regexp_like(col1, '[[:punct:]]*');


How do i search special characters in a string like say --inverted ? or a chinese character --can't print here.
This does not qualify any any of the above.

[:digit:]      Only the digits 0 to 9
[:alnum:]      Any alphanumeric character 0 to 9 OR A to Z or a to z.
[:alpha:]      Any alpha character A to Z or a to z.
[:blank:]      Space and TAB characters only.
[:xdigit:]     Hexadecimal notation 0-9, A-F, a-f.
[:punct:]      Punctuation symbols
0
gram77
Asked:
gram77
  • 3
  • 2
1 Solution
 
käµfm³d 👽Commented:
Following gives me all rows including 1 & 2 which are digits, why?
select * from taba
where regexp_like(col1, '[[:alnum:]]');
Because of how you structured the pattern (and its a REGEXP_LIKE). If a an alphabetic or numeric (i.e. "alnum") occurs *anywhere* within the target column's value, then a match will be declared.

Following gives me all rows including 1 & 2 which are digits, why?
select * from taba
where regexp_like(col1, '[[:punct:]]*');
You are looking for any punctuation mark. However, you marked this as "zero or more" (i.e. * ). This means that you will trivially match any column's value, since any column can have zero or more punctuation marks.

How do i search special characters in a string like say --inverted ? or a chinese character --can't print here.
I'm not certain of this, but you might try:

...where regexp_like(col1, '[^[:alpha:]]');

Open in new window

0
 
gram77Author Commented:
--why does the following show me 2a, 2? and 2; these are not digits.
How can i search for only digits using posix
select *
from taba
where regexp_like (col1, '[[:digit:]]');
0
 
käµfm³d 👽Commented:
"2" is a digit.

I believe your confusion is that you confusing "digits" with "numbers". "2a" is not a number, but it does contain a digit--2. That is why your pattern matches. To match a number you would do something like:

select *
from taba
where regexp_like (col1, '^[[:digit:]]+$');

Open in new window


Where ^ matches the start of a string, $ matches the end of a string, and + means "one or more" of the thing to the left, which in this case is "any digit" ( [[:digit:]] ).
0
 
gram77Author Commented:
As we have the following character classes,
[:digit:]      Only the digits 0 to 9
[:alnum:]      Any alphanumeric character 0 to 9 OR A to Z or a to z.
[:alpha:]      Any alpha character A to Z or a to z.
[:blank:]      Space and TAB characters only.
[:xdigit:]     Hexadecimal notation 0-9, A-F, a-f.
[:punct:]      Punctuation symbols

Q1. I want to search for anything that is not a digit, alphabet, punctuation, or blank
Q2. I want to search for special characters in the data like - inverted ? or say chinese chracters
0
 
käµfm³d 👽Commented:
A1
[^[:alnum:][:punct:][:blank:]]

Open in new window


A2
You can try:
[^[:digit:][:alnum:][:alpha:][:blank:][:xdigit:][:punct:]]

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now