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.

SQL: substring of a string field - get the first number of characters of a string in Oracle

For example, you would like a list of employees whose first names start with "Chr", the SUBSTR function would help you to achieve it.

SUBSTR takes three arguments, the field name, the beginning position (starting from 1), and the length of the substring.

SQL> select firstName, middleName, lastName, title, department from employee
           where SUBSTR (firstName, 1, 3) = 'Chr';

The output will look something like:

firstName                middleName                   lastName            title                    department
----------------           ------------------                ---------------      ------------             --------------
Christine                  Anna                              Arendt                   librarian               Library
Christopher                                                     Jones                  security guard        Security


If you would like to display only the first four characters of the department:

SQL> select firstName, middleName, lastName, title, SUBSTR(department, 1,4) as department
           from employee
           where SUBSTR (firstName, 1, 3) = 'Chr';

The output would look something like:

firstName                middleName                   lastName            title                    department
----------------           ------------------                ---------------      ------------             --------------
Christine                  Anna                              Arendt                   librarian               Libr
Christopher                                                     Jones                  security guard        Secu


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

                        
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.