PostgreSQL String() Function: Display a couple of columns which contain a character 'C' in a specific column after 2nd position

12. 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.

Sample Solution:

Code:

-- This SQL query retrieves the first name and last name of employees where the character 'C' appears after the second position in the last name.

SELECT first_name, last_name -- Selects the first_name and last_name columns
FROM employees -- Specifies the table from which to retrieve data, in this case, the employees table
WHERE POSITION('C' IN last_name) > 2; -- Filters the rows to include only those where the character 'C' appears after the second position in the last name

Explanation:

• This SQL query retrieves data from the employees table.
• The SELECT statement selects the first_name and last_name columns.
• 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 character 'C' appears after the second position in the last_name column.
• The POSITION() function finds the position of the character 'C' in the last_name column.
• The condition POSITION('C' IN last_name) > 2 checks if the position of 'C' in the last_name column is greater than 2, indicating that 'C' appears after the second position.
• The result set will contain the first name and last name for employees where the character 'C' appears after the second position in the last name.

Sample table: employees

Output:

pg_exercises=# SELECT first_name, last_name
pg_exercises-# FROM employees
pg_exercises-# WHERE POSITION('C' IN last_name) > 2;
 first_name | last_name
------------+-----------
 Samuel     | McCain
(1 row)

Practice Online

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

Previous: Write a query to display the first word in the job title if the job title contains more than one words.
Next: 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.