RIGHT()

Syntax

How it works

RIGHT(string, n) extracts a fixed number of characters from the end of a string. RIGHT('payment', 4) returns ment. It is the direct counterpart of LEFT(), which takes characters from the start instead.

Anything RIGHT() can do is also expressible with SUBSTRING() using a computed start position. RIGHT(str, n) is equivalent to SUBSTRING(str, LENGTH(str) - n + 1, n): you start n characters before the end and take the rest. RIGHT() is simply the shorter, clearer way to write that common case.

One portability note: SQLite has no RIGHT() function. There you use SUBSTR() with a negative start index, as in SUBSTR(str, -4), which counts back from the end and returns the last 4 characters. MySQL, PostgreSQL and SQL Server all provide RIGHT() natively.

Examples

Last four digits of a card number

-- Never store or show full card numbers; the last 4 is enough to identify one
SELECT RIGHT(card_number, 4) AS last4
FROM payment_methods;

last4
-----
4242
0005
1881

Pull the file extension

-- The last 3 characters of common uploads
SELECT filename, RIGHT(filename, 3) AS ext
FROM uploads;

filename    | ext
------------+----
 report.pdf | pdf
 photo.jpg  | jpg
 data.csv   | csv

Last N characters of any text

SELECT sku, RIGHT(sku, 5) AS tail
FROM products;

sku          | tail
-------------+------
 AB-2026-991 | 6-991
 CD-2026-004 | 6-004

Mask an account number, showing only the end

-- Replace everything but the final 4 characters with asterisks
SELECT
  CONCAT('****', RIGHT(account_no, 4)) AS masked
FROM accounts;

masked
----------
****9931
****0027

RIGHT rewritten with SUBSTRING

-- These two columns return the same value
SELECT
  RIGHT(email, 4)                                  AS with_right,
  SUBSTRING(email, CHAR_LENGTH(email) - 4 + 1, 4)  AS with_substring
FROM users;

with_right | with_substring
-----------+---------------
 .com      | .com
 .org      | .org

Common mistakes

-- Expecting an error or a padded value when n is too big
-- 'ab' has length 2, so RIGHT('ab', 5) does NOT return 5 chars
SELECT RIGHT('ab', 5);  -- returns 'ab', not '   ab'

-- If you need a fixed width, pad first, then take the right side
SELECT RIGHT(CONCAT('     ', 'ab'), 5);  -- '   ab'

-- SQLite has no RIGHT() function; this raises an error there
SELECT RIGHT(phone, 4) FROM contacts;

-- In SQLite, use SUBSTR with a negative start index
SELECT SUBSTR(phone, -4) FROM contacts;

-- Wanting the FIRST characters but reaching for RIGHT
SELECT RIGHT(country_code, 2) AS prefix
FROM addresses;  -- gives the LAST 2, not the first 2

-- Use LEFT() when you want characters from the start
SELECT LEFT(country_code, 2) AS prefix
FROM addresses;

Performance

RIGHT() is a cheap, per row string operation, so it costs almost nothing on the values it touches. The real cost comes when you use it in a WHERE clause. A predicate like WHERE RIGHT(email, 4) = '.com' wraps the column in a function, which prevents the optimiser from using a plain index on that column and forces a full scan.

If you frequently filter or group by the tail of a string, store that suffix in its own column or build an expression based index (a functional index in PostgreSQL, a computed persisted column in SQL Server) on RIGHT(col, n). See the indexing guide for how functional indexes let these lookups stay fast.

Interview questions

SELECT SUBSTRING(str, CHAR_LENGTH(str) - 4 + 1, 4) AS same_as_right4
FROM t;