PostgreSQL String() Function: Find the details of employees who contain eight or more characters in their first name

3. Write a query to find the details of those employees who contain eight or more characters in their first name.

Sample Solution:

Code:

-- This SQL query retrieves all columns from the employees table where the length of the first_name is 8 characters or more.

SELECT * -- Selects all columns from the employees table
FROM employees -- Specifies the table from which to retrieve data, in this case, the employees table
WHERE LENGTH(first_name) >= 8; -- Filters the rows to include only those where the length of the first_name is 8 characters or more

Explanation:

• This SQL query retrieves data from the employees table.
• The SELECT * statement selects all columns from the employees table.
• The FROM clause specifies the table from which to retrieve the data, which is the employees table.
• The WHERE clause filters the rows to include only those where the length of the first_name column is 8 characters or more.
• The LENGTH(first_name) >= 8 condition checks if the length of the first_name column is greater than or equal to 8 characters.
• The result set will contain all columns for rows where the length of the first_name is 8 characters or more.

Sample table: employees

Output:

pg_exercises=# SELECT *
pg_exercises-# FROM employees
pg_exercises-# WHERE LENGTH(first_name) >= 8;
 employee_id | first_name  | last_name |     email     |    phone_number    | hire_date  |   job_id   |  salary  | commission_pct | manager_id | department_id |
-------------+-------------+-----------+---------------+--------------------+------------+------------+----------+----------------+------------+---------------|
         103 | Alexander   | Hunold    | not available | 590.423.4567       | 1987-06-20 | IT_PROG    |  9030.00 |           0.10 |        102 |            60 |
         115 | Alexander   | Khoo      | not available | 515.127.4562       | 1987-07-02 | PU_CLERK   |  3130.00 |           0.10 |        114 |            30 | 
         176 | Jonathon    | Taylor    | not available | 011.44.1644.429265 | 1987-09-01 | SA_REP     |  8630.00 |           0.10 |        149 |            80 |
         178 | Kimberely   | Grant     | not available | 011.44.1644.429263 | 1987-09-03 | SA_REP     |  7030.00 |           0.10 |        149 |             0 |
         153 | Christopher | Olsen     | not available | 011.44.1344.498718 | 1987-08-09 | SA_REP     |  8030.00 |           0.10 |        145 |            80 |
         189 | Jennifer    | Dilly     | not available | 650.505.2876       | 1987-09-14 | SH_CLERK   |  3630.00 |           0.10 |        122 |            50 |
         112 | Jose Manuel | Urman     | not available | 515.123.4469       | 1987-06-29 | FI_ACCOUNT |  7830.00 |           0.10 |        108 |           100 |
         169 | Harrison    | Bloom     | not available | 011.44.1343.829268 | 1987-08-25 | SA_REP     | 10030.00 |           0.10 |        148 |            80 |
         172 | Elizabeth   | Bates     | not available | 011.44.1343.529268 | 1987-08-28 | SA_REP     |  7330.00 |           0.10 |        148 |            80 |
         200 | Jennifer    | Whalen    | not available | 515.123.4444       | 1987-09-25 | AD_ASST    | 4400.00  |	     0.00 |   	   101 |            10 |

Practice Online

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query to update the phone_number column with '999' where the substring '124' found in that column.
Next: Write a query to fill the maximum and minimum salary with leading asterisks whether these two columns does not contain a seven digit number.