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