PostgreSQL String() Function: Display specific columns for all employees and form the salary column to be left-padded with the dollar

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

Sample Solution:

Code:

-- This SQL query retrieves the first name of employees along with a formatted representation of their salaries.

SELECT first_name, -- Selects the first_name column
LPAD(TRIM(TO_CHAR(salary,'9999999')), 7, '$') SALARY -- Formats the salary column with padding and '$' symbols
FROM employees; -- Specifies the table from which to retrieve data, in this case, the employees table

Explanation:

• This SQL query retrieves the first name of employees along with a formatted representation of their salaries.
• The SELECT statement selects the first_name column.
• The LPAD() function left-pads the formatted salary values with '$' symbols 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'.
• The salary column is assumed to store salary values.
• The result set will contain the first name of employees along with the formatted representation of their salaries, where salaries are padded with '$' symbols.

Sample table: employees

Output:

pg_exercises=# SELECT first_name,
pg_exercises-# LPAD(TRIM(TO_CHAR(salary,'9999999')), 7, '$') SALARY
pg_exercises-# FROM employees;
 first_name  | salary
-------------+---------
 Alexander   | $$$9000
 Bruce       | $$$6000
 David       | $$$4800
 Valli       | $$$4800
 Diana       | $$$4200
 Den         | $$11000
 Alexander   | $$$3100
 Shelli      | $$$2900
 Sigal       | $$$2800
 Steven      | $$24000
 Neena       | $$17000
 Guy         | $$$2600
 Karen       | $$$2500
 Jason       | $$$3300
 Michael     | $$$2900
 Ki          | $$$2400
 Hazel       | $$$2200
 Stephen     | $$$3200
 John        | $$$2700
 Joshua      | $$$2500
 Trenna      | $$$3500
...          | ...
 Susan       | $$$6500
 Hermann     | $$10000
 Shelley     | $$12000
 William     | $$$8300
(106 rows)

Practice Online

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

Previous: 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.
Next: PostgreSQL JOINS - Exercises, Practice, Solution