SQL: retrieve the first / last number of digits from a number field

A. Retrieve the first number of digits

If the values in your number field have a fixed length, you can divide the number by 10 to the p power, where p is the number of digits you want to remove from the end of the number.

For example you have a hireDate in your employee table, which is in the Julian format such as 2017030. If you just want to retrieve the year of employment, you can use the SQL below.

SQL> select floor(hireDate/1000) as Year from employee;

If the values in your field does not have a fixed length, the following SQL can serve you purpose of retrieving the first number of digits.

SQL> select departmentName from department where substr(to_char(departmentNo), 1, 2) = '14';

Use the substr function, you can get digits starting from any position. For example. your employee code has the first 3 digits represent the college, followed by another 3 digits representing the department, and another 3 digits for employee status. The SQL below will help you to retrieve the depart part of the employee code

SQL> select name, substr(to_char(employeeCode), 4, 3) as dept from employee;

To get the employee status:

SQL> select name, substr(to_char(employeeCode), 7, 3) as status from employee;

B. Retrieve the last number of digits

To retrieve the last three digits of the above employeeCode,

SQL> select name, substr(to_char(employeeCode), length(employeeCode) - 3, 3) as status from employee;

---------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.