PostgreSQL String() Function: Display the first word form a particular column

11. Write a query to display the first word in the job title if the job title contains more than one words.

Sample Solution:

Code:

-- This SQL query retrieves the job title and the substring up to the first space character in the job title.

SELECT job_title, -- Selects the job_title column
SUBSTR(job_title, 1, POSITION(' ' IN job_title)) -- Extracts the substring from the job_title column up to the first space character
FROM jobs; -- Specifies the table from which to retrieve data, in this case, the jobs table

Explanation:

• This SQL query extracts part of the job title from the jobs table.
• The SELECT statement selects the job_title column.
• The SUBSTR() function is used to extract a substring from the job_title column.
• The POSITION() function finds the position of the first space character in the job_title column.
• The SUBSTR() function then extracts characters from the job_title column starting from the first character up to the position of the first space character.
• The result set will contain the job title along with the extracted substring up to the first space character for each job in the jobs table.

Sample table: jobs

Output:

pg_exercises=# SELECT job_title,  SUBSTR(job_title,1, POSITION(' ' IN job_title))
pg_exercises-# FROM jobs;
            job_title            |     substr
---------------------------------+-----------------
 President                       |
 Administration Vice President   | Administration
 Administration Assistant        | Administration
 Finance Manager                 | Finance
 Accountant                      |
 Accounting Manager              | Accounting
 Public Accountant               | Public
 Sales Manager                   | Sales
 Sales Representative            | Sales
 Purchasing Manager              | Purchasing
 Purchasing Clerk                | Purchasing
 Stock Manager                   | Stock
 Stock Clerk                     | Stock
 Shipping Clerk                  | Shipping
 Programmer                      |
 Marketing Manager               | Marketing
 Marketing Representative        | Marketing
 Human Resources Representative  | Human
 Public Relations Representative | Public
(19 rows)

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

Previous: Write a query to get the information about those locations which contain the characters in its street address is on and below the minimum character length of street_address.
Next: 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.