I have a table with below structure:
Example_table(Customer_Num varchar2(50))
Some Example data in this table :
Example_table
----------------------------
Customer_Num
12445
12345
12ttd
2376y
23%%*
23467
I want to select custoemr_num records with digit characters , so these records 12ttd , 2376y , 23%%*
should not appear in the final result . Considering the fact that I can not change the table structure , which one is the correct query ?
Q-1:
select customer_num
from Example_table
where REGEXP_LIKE(customer_num, '[[:digit:]]')
Q-2:
select customer_num
from Example_table
where REGEXP_LIKE(customer_num, '[0-9]')
Are there any special condition in which these queries might end up producing wrong results?
Thanks in advance.
Best Answer
Neither is correct because both only check for a single digit that can appear anywhere in the string.
A regex that only selects digits in the complete string would be:
^
anchors the expression at the beginning of the string (to avoid leading non-digits)[0-9]+
matches multiple digits but at least one$
anchors the expression at the end of the string (to avoid trailing non-digits)There is no difference between using
[0-9]
and using[[:digit:]]