0

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.