PostgreSQL LEFT() function
LEFT() function
The PostgreSQL left() function is used to extract n number of characters specified in the argument from the left of a given string. When the value of n is negative, the extraction will happen from left except last n characters.
Uses of LEFT() Function
- Extracting Substrings: Retrieve a specific number of characters from the start of a string.
- Handling Negative Values: Extract characters from the left side while excluding a specified number of characters from the end.
- Filtering Data: Use in WHERE clauses to filter results based on a prefix match.
- Data Formatting: Format and display portions of text data in a structured manner.
- Combining with Other Functions: Use alongside other string functions for complex text manipulations.
- Data Validation: Check for specific patterns or prefixes in text data for validation purposes.
Syntax:
left(string,n)
PostgreSQL Version: 9.3
Visual Presentation of PostgreSQL LEFT() function
Example: PostgreSQL LEFT() function:
In the example below, the left function extracted 3 characters from the left of the string 'w3resource'.
SQL Code:
SELECT left('w3resource',3)
AS "Extract 3 characters from the left";
Output:
Extract 3 characters from the left ------------------------------------ w3r (1 row)
Example: PostgreSQL LEFT() function using negative value:
In the example below, the left function extracted all the characters from the left side except 3 rightmost characters from the string 'w3resource', because the value of extracting character number is negative.
SQL Code:
SELECT left('w3resource',-3)
AS "Extract all characters except 3 rightmost";
Output:
Extract all characters except 3 rightmost ------------------------------------------- w3resou (1 row)
Example: PostgreSQL LEFT() function using columns of table:
Sample Table: employees.
employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------
100 | Steven | King | SKING | 515.123.4567 | 1987-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90
101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 1987-06-18 | AD_VP | 17000.00 | 0.00 | 100 | 90
102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 1987-06-19 | AD_VP | 17000.00 | 0.00 | 100 | 90
103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 1987-06-20 | IT_PROG | 9000.00 | 0.00 | 102 | 60
104 | Bruce | Ernst | BERNST | 590.423.4568 | 1987-06-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60
105 | David | Austin | DAUSTIN | 590.423.4569 | 1987-06-22 | IT_PROG | 4800.00 | 0.00 | 103 | 60
106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 1987-06-23 | IT_PROG | 4800.00 | 0.00 | 103 | 60
107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 1987-06-24 | IT_PROG | 4200.00 | 0.00 | 103 | 60
108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 1987-06-25 | FI_MGR | 12000.00 | 0.00 | 101 | 100
109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 1987-06-26 | FI_ACCOUNT | 9000.00 | 0.00 | 108 | 100
..................
206 | William | Gietz | WGIETZ | 515.123.8181 | 1987-10-01 | AC_ACCOUNT | 8300.00 | 0.00 | 205 | 110
If we want to display the first_name, last_name for those employees whose first 3 characters of their name contain 'Nan' from employees table, the following sql statement can be used:
SQL Code:
SELECT first_name, last_name
FROM employees
WHERE left(first_name,3)='Nan';
Output:
first_name | last_name ------------+----------- Nancy | Greenberg Nanette | Cambrault Nandita | Sarchand (3 rows)
PREV : INITCAP function
NEXT : LENGTH function