When you need the first three digits of a code or the last four digits of a phone number, reaching for a verbose SUBSTRING is overkill. PostgreSQL gives you two short functions: LEFT takes characters from the start of a string, RIGHT takes them from the end. Let's work through them and find the sharp edges along the way.
Basic syntax
Both functions take a string and a character count: LEFT(string, n) and RIGHT(string, n). They count characters, not bytes, so UTF-8 is not a problem.
SELECT LEFT('PROMO-2026', 5) AS head,
RIGHT('PROMO-2026', 4) AS tail;
On a real table this is handy for pulling out prefixes and suffixes. Say orders.status holds a string like paid-eu and you want the leading letters of the status:
SELECT id,
LEFT(status, 4) AS status_head
FROM orders
LIMIT 5;
Key properties:
- The result type is
text.
- If
n is larger than the string length, you get the whole string back, with no error and no padding.
- A
NULL input gives a NULL output.
Masking a card number
The classic case is showing only the last four digits of a payment card and hiding the rest. Here RIGHT is exactly the tool:
SELECT RIGHT('4111111111114242', 4) AS last4;
To get the familiar **** 4242 format, concatenate a mask with the tail. Assume there is a column holding the user's card number:
SELECT u.id,
'**** ' || RIGHT(card_number, 4) AS masked_card
FROM users u
JOIN payment_methods pm ON pm.user_id = u.id;
Likewise LEFT helps extract the BIN, the first six digits that identify the issuing bank:
SELECT LEFT(card_number, 6) AS bin
FROM payment_methods;
Gotcha: LEFT and RIGHT cut characters, not "logical" parts. If the number contains spaces or dashes (4111 1111 1111 4242), RIGHT(..., 4) returns 4242 only when the tail is clean. Strip separators with REPLACE first, or you might accidentally show a space instead of a digit.
Negative length
This is the most underrated feature. If you pass a negative n, PostgreSQL counts characters from the opposite end and returns the remainder.
LEFT(s, -k) returns the string without its last k characters.RIGHT(s, -k) returns the string without its first k characters.
SELECT LEFT('order-12345', -5) AS without_tail,
RIGHT('order-12345', -6) AS without_head;
This shines when you know the length of the part to trim but not the length of the string. For example, drop a fixed domain suffix or remove a known prefix. Be careful, though: if -k is larger in magnitude than the string length, you get an empty string '', not NULL, so it is easy to lose data silently.
LEFT and RIGHT vs SUBSTRING
SUBSTRING is more powerful but wordier. Any LEFT/RIGHT can be expressed with it:
SELECT SUBSTRING(name FROM 1 FOR 3) FROM users;
SELECT SUBSTRING(name FROM length(name) - 3 + 1) FROM users;
Notice the RIGHT equivalent forces you to compute length() by hand and makes off-by-one errors easy. So the rule is simple:
- Need a fixed prefix or suffix: use
LEFT or RIGHT, the code reads instantly.
- Need a slice from the middle or a dynamic position: use
SUBSTRING.
- Need trimming by a pattern rather than a length: look at
split_part or regular expressions.
A realistic example: group employees by the first letter of their department with LEFT, which is far cleaner than SUBSTRING:
SELECT LEFT(dept, 1) AS dept_initial,
count(*) AS people
FROM employees
GROUP BY LEFT(dept, 1)
ORDER BY dept_initial;
MySQL and ClickHouse
Good news: LEFT and RIGHT exist in MySQL with the same LEFT(str, len) and RIGHT(str, len) syntax, so queries port over with almost no edits.
- MySQL does not support negative length:
LEFT('abc', -1) returns an empty string, not "everything except the last character." This is the main difference when migrating.
- ClickHouse also has
left and right; a negative argument there works like PostgreSQL, counting from the end.
- In MySQL both functions count characters according to the column's current character set, which usually matches PostgreSQL's behavior for UTF-8.
SELECT LEFT(email, 3) AS prefix,
RIGHT(email, 3) AS suffix
FROM users;
Bottom line: LEFT and RIGHT are the fastest way to grab the edge of a string. Remember negative length as "trim from the other end," always clean separators before masking, and keep in mind that the negative argument does not work in MySQL.
When you need the first three digits of a code or the last four digits of a phone number, reaching for a verbose
SUBSTRINGis overkill. PostgreSQL gives you two short functions:LEFTtakes characters from the start of a string,RIGHTtakes them from the end. Let's work through them and find the sharp edges along the way.Basic syntax
Both functions take a string and a character count:
LEFT(string, n)andRIGHT(string, n). They count characters, not bytes, so UTF-8 is not a problem.SELECT LEFT('PROMO-2026', 5) AS head, -- 'PROMO' RIGHT('PROMO-2026', 4) AS tail; -- '2026'On a real table this is handy for pulling out prefixes and suffixes. Say
orders.statusholds a string likepaid-euand you want the leading letters of the status:SELECT id, LEFT(status, 4) AS status_head FROM orders LIMIT 5;Key properties:
text.nis larger than the string length, you get the whole string back, with no error and no padding.NULLinput gives aNULLoutput.Masking a card number
The classic case is showing only the last four digits of a payment card and hiding the rest. Here
RIGHTis exactly the tool:SELECT RIGHT('4111111111114242', 4) AS last4; -- '4242'To get the familiar
**** 4242format, concatenate a mask with the tail. Assume there is a column holding the user's card number:SELECT u.id, '**** ' || RIGHT(card_number, 4) AS masked_card FROM users u JOIN payment_methods pm ON pm.user_id = u.id;Likewise
LEFThelps extract the BIN, the first six digits that identify the issuing bank:SELECT LEFT(card_number, 6) AS bin FROM payment_methods;Gotcha:
LEFTandRIGHTcut characters, not "logical" parts. If the number contains spaces or dashes (4111 1111 1111 4242),RIGHT(..., 4)returns4242only when the tail is clean. Strip separators withREPLACEfirst, or you might accidentally show a space instead of a digit.Negative length
This is the most underrated feature. If you pass a negative
n, PostgreSQL counts characters from the opposite end and returns the remainder.LEFT(s, -k)returns the string without its lastkcharacters.RIGHT(s, -k)returns the string without its firstkcharacters.SELECT LEFT('order-12345', -5) AS without_tail, -- 'order-' RIGHT('order-12345', -6) AS without_head; -- '12345'This shines when you know the length of the part to trim but not the length of the string. For example, drop a fixed domain suffix or remove a known prefix. Be careful, though: if
-kis larger in magnitude than the string length, you get an empty string'', notNULL, so it is easy to lose data silently.LEFT and RIGHT vs SUBSTRING
SUBSTRINGis more powerful but wordier. AnyLEFT/RIGHTcan be expressed with it:-- LEFT(name, 3) is equivalent to: SELECT SUBSTRING(name FROM 1 FOR 3) FROM users; -- RIGHT(name, 3) is equivalent to: SELECT SUBSTRING(name FROM length(name) - 3 + 1) FROM users;Notice the
RIGHTequivalent forces you to computelength()by hand and makes off-by-one errors easy. So the rule is simple:LEFTorRIGHT, the code reads instantly.SUBSTRING.split_partor regular expressions.A realistic example: group employees by the first letter of their department with
LEFT, which is far cleaner thanSUBSTRING:SELECT LEFT(dept, 1) AS dept_initial, count(*) AS people FROM employees GROUP BY LEFT(dept, 1) ORDER BY dept_initial;MySQL and ClickHouse
Good news:
LEFTandRIGHTexist in MySQL with the sameLEFT(str, len)andRIGHT(str, len)syntax, so queries port over with almost no edits.LEFT('abc', -1)returns an empty string, not "everything except the last character." This is the main difference when migrating.leftandright; a negative argument there works like PostgreSQL, counting from the end.-- Works identically in PostgreSQL and MySQL SELECT LEFT(email, 3) AS prefix, RIGHT(email, 3) AS suffix FROM users;Bottom line:
LEFTandRIGHTare the fastest way to grab the edge of a string. Remember negative length as "trim from the other end," always clean separators before masking, and keep in mind that the negative argument does not work in MySQL.