Search for numbers with the same last 4 digits in Oracle database

Posted on July 19, 2006

0


select * from a_number num 
      where to_number(substr(num.a_number, 7,4),'9999') 
      = to_number(substr(num.a_number, 10,1),'9')*1111

Am I smart?? Remember:

  • substr(num.a_number, 7,4): take a sub string of a_number from position 7 until the end (the length 4).
  • to_number(‘1234’, ‘9999’): format the string ‘1234’ to pattern ‘nnnn’.
Posted in: Development