MySQL String Exercises: Display the first word from the job titles which contains more than one words

MySQL String: Exercise-12 with Solution

Write a MySQL query to display the first word from those job titles which contains more than one words.

+------------+---------------------------------+------------+------------+
| JOB_ID     | JOB_TITLE                       | MIN_SALARY | MAX_SALARY |
+------------+---------------------------------+------------+------------+
| AD_PRES    | President                       |      20000 |      40000 |
| AD_VP      | Administration Vice President   |      15000 |      30000 |
| AD_ASST    | Administration Assistant        |       3000 |       6000 |
| FI_MGR     | Finance Manager                 |       8200 |      16000 |
| FI_ACCOUNT | Accountant                      |       4200 |       9000 |
| AC_MGR     | Accounting Manager              |       8200 |      16000 |
| AC_ACCOUNT | Public Accountant               |       4200 |       9000 |
| SA_MAN     | Sales Manager                   |      10000 |      20000 |
| SA_REP     | Sales Representative            |       6000 |      12000 |
| PU_MAN     | Purchasing Manager              |       8000 |      15000 |
| PU_CLERK   | Purchasing Clerk                |       2500 |       5500 |
| ST_MAN     | Stock Manager                   |       5500 |       8500 |
| ST_CLERK   | Stock Clerk                     |       2000 |       5000 |
| SH_CLERK   | Shipping Clerk                  |       2500 |       5500 |
| IT_PROG    | Programmer                      |       4000 |      10000 |
| MK_MAN     | Marketing Manager               |       9000 |      15000 |
| MK_REP     | Marketing Representative        |       4000 |       9000 |
| HR_REP     | Human Resources Representative  |       4000 |       9000 |
| PR_REP     | Public Relations Representative |       4500 |      10500 |
+------------+---------------------------------+------------+------------+

Code:

-- This SQL query selects a portion of the job title from the jobs table.

SELECT 
job_title, -- Selecting the job title from the jobs table.

    -- Extracting the substring from the job title starting from the first character up to the position of the first space.
SUBSTR(job_title, 1, INSTR(job_title, ' ') - 1) 

FROM 
jobs; -- Specifies the table from which data is being retrieved, in this case, it's the 'jobs' table.

Explanation:

• The SELECT statement retrieves data from the specified table (jobs).
• job_title column is selected directly.
• The SUBSTR() function is used to extract a portion of the job_title.
• It starts from the first character (1) and its length is determined by the position of the first space (' ') found in the job_title, minus one, to exclude the space itself.
• The INSTR() function is used to find the position of the first occurrence of a space in the job_title.
• The result is the substring representing the part of the job title before the first space, typically indicating the job's main category or title.

Sample Output:

job_title	    				SUBSTR(job_title,1, INSTR(job_title, ' ')-1)
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 Representatives			Marketing
Human Resources Representative			Human
Public Relations Representative			Public

MySQL Code Editor:

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

Previous:Write a MySQL query to get the locations that have minimum street length.
Next:Write a MySQL query to display the length of first name for employees where last name contain character 'c' after 2nd position.