PostgreSQL String() Function: Fill the maximum and minimum salary with leading asterisks until it is becoming a seven character string

4. Write a query to fill the maximum and minimum salary with leading asterisks whether these two columns does not contain a seven digit number.

Sample Solution:

Code:

-- This SQL query retrieves job IDs along with formatted maximum and minimum salaries from the jobs table.

SELECT job_id, -- Selects the job_id column
LPAD(trim(to_char(max_salary,'9999999')),7,'*') "Max Salary", -- Formats the maximum salary column with padding and asterisks
LPAD(trim(to_char(min_salary,'9999999')),7,'*') "Min Salary" -- Formats the minimum salary column with padding and asterisks
FROM jobs; -- Specifies the table from which to retrieve data, in this case, the jobs table

Explanation:

• This SQL query retrieves job IDs along with formatted maximum and minimum salaries from the jobs table.
• The SELECT statement selects the job_id column along with formatted maximum and minimum salaries.
• The LPAD() function left-pads the formatted salary values with asterisks to a total width of 7 characters.
• The trim() function removes leading and trailing spaces from the formatted salary values.
• The to_char() function converts the numeric salary values to character strings with the specified format '9999999'.
• max_salary and min_salary are assumed to be numeric columns storing salary values.
• The result set will contain the job ID, maximum salary, and minimum salary for each job from the jobs table, with the salaries formatted and padded with asterisks.

Sample table: jobs

Output:

pg_exercises=# SELECT job_id,  LPAD(trim(to_char(max_salary,'9999999')),7,'*') "Max Salary" ,
pg_exercises-# LPAD(trim(to_char(min_salary,'9999999')),7,'*') "Min Salary"
pg_exercises-# FROM jobs;
   job_id   | Max Salary | Min Salary
------------+------------+------------
 AD_PRES    | **40000    | **20000
 AD_VP      | **30000    | **15000
 AD_ASST    | ***6000    | ***3000
 FI_MGR     | **16000    | ***8200
 FI_ACCOUNT | ***9000    | ***4200
 AC_MGR     | **16000    | ***8200
 AC_ACCOUNT | ***9000    | ***4200
 SA_MAN     | **20000    | **10000
 SA_REP     | **12000    | ***6000
 PU_MAN     | **15000    | ***8000
 PU_CLERK   | ***5500    | ***2500
 ST_MAN     | ***8500    | ***5500
 ST_CLERK   | ***5000    | ***2000
 SH_CLERK   | ***5500    | ***2500
 IT_PROG    | **10000    | ***4000
 MK_MAN     | **15000    | ***9000
 MK_REP     | ***9000    | ***4000
 HR_REP     | ***9000    | ***4000
 PR_REP     | **10500    | ***4500
(19 rows)

Practice Online

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

Previous: Write a query to find the details of those employees who contain eight or more characters in their first name.
Next: Write a query to join the text '@example.com' with the email column.