PostgreSQL String() Function: Display the first name and length of first name which starts with the letters 'A', 'J' or 'M'

13. Write a query that displays the first name and the character length of the first name for all employees whose name starts with the letters 'A', 'J' or 'M'. Give each column an appropriate label. Sort the results by the employees' first names.

Sample Solution:

Code:

-- This SQL query retrieves the first name and its length for employees whose first name starts with 'J', 'M', or 'A', ordered alphabetically by first name.

SELECT first_name "Name", -- Selects the first_name column and labels it as "Name"
LENGTH(first_name) "Length" -- Calculates the length of the first_name column and labels it as "Length"
FROM employees -- Specifies the table from which to retrieve data, in this case, the employees table
WHERE first_name LIKE 'J%' -- Filters the rows to include only those where the first name starts with 'J'
   OR first_name LIKE 'M%' -- Filters the rows to include only those where the first name starts with 'M'
   OR first_name LIKE 'A%' -- Filters the rows to include only those where the first name starts with 'A'
ORDER BY first_name ; -- Orders the results alphabetically by first name

Explanation:

• This SQL query retrieves the first name and its length for employees whose first name starts with 'J', 'M', or 'A', ordered alphabetically by first name.
• The SELECT statement selects the first_name column and calculates its length, labeling it as "Name" and "Length" respectively.
• 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 first name starts with 'J', 'M', or 'A' using the LIKE operator.
• The % symbol is a wildcard character used with the LIKE operator to match any sequence of characters.
• The ORDER BY clause orders the results alphabetically by the first name.
• The result set will contain the first name and its length for employees whose first name starts with 'J', 'M', or 'A', ordered alphabetically by first name.

Sample table: employees

Output:

pg_exercises=# SELECT first_name "Name",
pg_exercises-# LENGTH(first_name) "Length"
pg_exercises-# FROM employees
pg_exercises-# WHERE first_name LIKE 'J%'
pg_exercises-# OR first_name LIKE 'M%'
pg_exercises-# OR first_name LIKE 'A%'
pg_exercises-# ORDER BY first_name ;
    Name     | Length
-------------+--------
 Adam        |      4
 Alana       |      5
 Alberto     |      7
 Alexander   |      9
 Alexander   |      9
 Alexis      |      6
 Allan       |      5
 Alyssa      |      6
 Amit        |      4
 Anthony     |      7
 Jack        |      4
 James       |      5
 James       |      5
 Janette     |      7
 Jason       |      5
 Jean        |      4
 Jennifer    |      8
 Jennifer    |      8
 John        |      4
 John        |      4
 John        |      4
 Jonathon    |      8
 Jose Manuel |     11
 Joshua      |      6
 Julia       |      5
 Julia       |      5
 Martha      |      6
 Mattea      |      6
 Matthew     |      7
 Michael     |      7
 Michael     |      7
 Mozhe       |      5
(32 rows)

Practice Online

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

Previous: Write a query to display the first name, last name for the employees, which contain a letter 'C' to their last name at 3rd or greater position.
Next: Write a query to display the first name and salary for all employees. Form the salary to be 10 characters long, left-padded with the $ symbol. Label the column as SALARY.