Query to select 'Customer_Num' with digit characters using regular expression

Question

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.

Answer 1

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:

where REGEXP_LIKE(customer_num, '^[0-9]+$')

• ^ 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:]]