Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 12 of 49

SQL string functions: join, trim, cut and search

Change and inspect text in SQL with UPPER, LOWER, LENGTH, TRIM, SUBSTR, REPLACE and string joining, with checked output and a database differences note.

15 minFunctions and expressionsUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonJoin, clean, cut and search text values in a query using portable string functions, and know where each database spells them differently.

After this lesson you can clean up and reshape text in a query: change case, join columns into one label, cut a piece out of a value, replace words, and count characters. Text columns are everywhere in real databases (names, emails, titles), and the raw values are rarely in exactly the shape a report needs.

Every query here runs against the sample company database from the earlier lesson. The examples were run on SQLite, and the note near the end lists where other databases spell things differently.

What a function does

A function takes one or more values, does something with them, and hands back one value. In UPPER(last_name) the input is the last_name of the current row, and the output is that name in capitals. The database applies the function to every row the query returns.

The important point for a beginner is that functions in a SELECT list only change the result. The table is untouched.

sql
SELECT first_name,
       UPPER(last_name)  AS shout,
       LOWER(job_title)  AS lower_title,
       LENGTH(email)     AS email_length
FROM employees
WHERE id <= 4;
result
+------------+-------+----------------------+--------------+
| first_name | shout |     lower_title      | email_length |
+------------+-------+----------------------+--------------+
| Meera      | IYER  | engineering director |           22 |
| Arjun      | RAO   | senior developer     |           21 |
| Kavya      | NAIR  | senior developer     |           22 |
| Rohan      | DAS   | developer            |           21 |
+------------+-------+----------------------+--------------+

UPPER and LOWER change case, and LENGTH counts characters. UPPER and LOWER exist under these names in every major database. LENGTH has a few spelling and counting differences, described in the differences note.

Joining text together

Gluing values into one piece of text is called concatenation. The standard operator is two pipe characters:

sql
SELECT first_name || ' ' || last_name AS full_name,
       email
FROM employees
WHERE id <= 3;
result
+------------+------------------------+
| full_name  |         email          |
+------------+------------------------+
| Meera Iyer | meera.iyer@example.com |
| Arjun Rao  | arjun.rao@example.com  |
| Kavya Nair | kavya.nair@example.com |
+------------+------------------------+

Notice that the space between the names is just another piece of text, ' '. Nothing is added for you. You can mix columns and literal text freely, and you can build a label with punctuation:

sql
SELECT SUBSTR(first_name, 1, 1) || '. ' || last_name AS short_name,
       job_title
FROM employees
WHERE id <= 5;
result
+------------+----------------------+
| short_name |      job_title       |
+------------+----------------------+
| M. Iyer    | Engineering Director |
| A. Rao     | Senior Developer     |
| K. Nair    | Senior Developer     |
| R. Das     | Developer            |
| I. Kapoor  | Developer            |
+------------+----------------------+

Here SUBSTR(first_name, 1, 1) takes one character starting at position 1, which is the initial. The next section explains that function properly.

Cutting and searching

SUBSTR(text, start, length) returns part of a text value. Positions start at 1, not 0. If you leave out the length, you get everything from the start position to the end.

INSTR(text, search) returns the position where search first appears, or 0 when it is not there. Put the two together and you can split an email address at the @:

sql
SELECT email,
       INSTR(email, '@')                      AS at_position,
       SUBSTR(email, 1, INSTR(email, '@') - 1) AS user_part,
       SUBSTR(email, INSTR(email, '@') + 1)    AS domain
FROM employees
WHERE id <= 3;
result
+------------------------+-------------+------------+-------------+
|         email          | at_position | user_part  |   domain    |
+------------------------+-------------+------------+-------------+
| meera.iyer@example.com |          11 | meera.iyer | example.com |
| arjun.rao@example.com  |          10 | arjun.rao  | example.com |
| kavya.nair@example.com |          11 | kavya.nair | example.com |
+------------------------+-------------+------------+-------------+

Read the user_part expression from the inside out: find the @, subtract one to stop just before it, and take that many characters from the start. The domain expression starts one character after the @ and runs to the end.

Functions can sit inside a WHERE clause or a GROUP BY as well, because they are just expressions:

sql
SELECT DISTINCT SUBSTR(email, INSTR(email, '@') + 1) AS domain
FROM employees;
result
+-------------+
|   domain    |
+-------------+
| example.com |
+-------------+

All twenty people share one domain. That makes sense for a made-up company, and it is a good quick check that the data looks the way you expect.

Replacing and trimming

REPLACE(text, old, new) swaps every occurrence of old for new. Depending on the database and its settings it may be case sensitive, so check your data before relying on it:

sql
SELECT job_title,
       REPLACE(job_title, 'Developer', 'Dev') AS shorter
FROM employees
WHERE id IN (2, 4, 19);
result
+------------------+------------+
|    job_title     |  shorter   |
+------------------+------------+
| Senior Developer | Senior Dev |
| Developer        | Dev        |
| Junior Developer | Junior Dev |
+------------------+------------+

TRIM removes spaces from both ends of a value. LTRIM and RTRIM do only the left or the right side. Spaces at the edges are one of the most common reasons two values that look equal do not match, so TRIM is worth remembering. To make the spaces visible, the next query wraps each result in square brackets:

sql
SELECT '[' || TRIM('   hi  ')  || ']' AS both_sides,
       '[' || LTRIM('   hi  ') || ']' AS left_only,
       '[' || RTRIM('   hi  ') || ']' AS right_only;
result
+------------+-----------+------------+
| both_sides | left_only | right_only |
+------------+-----------+------------+
| [hi]       | [hi  ]    | [   hi]    |
+------------+-----------+------------+

The brackets show exactly where the text starts and ends, so you can see which spaces each function removed.

Searching without caring about case

Whether = and LIKE ignore capital letters depends on the database and its settings. A habit that works everywhere is to lower-case both sides of the comparison:

sql
SELECT first_name, last_name
FROM employees
WHERE LOWER(last_name) = 'iyer';
result
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Meera      | Iyer      |
+------------+-----------+

This matches Iyer, IYER and iyer in every database. One cost to know about: wrapping a column in a function usually stops the database from using an ordinary index on that column, so on very large tables you would store the text in a consistent case instead.

NULL and text

Join anything to NULL and the answer is NULL. Most string functions behave the same way: give them NULL and you get NULL back.

sql
SELECT UPPER(NULL)    AS upper_of_null,
       'Hi, ' || NULL AS joined_with_null,
       LENGTH(NULL)   AS length_of_null;
result
+---------------+------------------+----------------+
| upper_of_null | joined_with_null | length_of_null |
+---------------+------------------+----------------+
| NULL          | NULL             | NULL           |
+---------------+------------------+----------------+

When you build a label from a column that can be empty, use COALESCE to supply a replacement. You will meet it in the lesson on CAST, COALESCE and NULLIF.

Differences between databases

String handling is one of the places where databases disagree the most. These are the differences you are most likely to hit:

  • Joining text. || works in standard SQL, PostgreSQL and SQLite. In MySQL, || means logical OR by default, so use CONCAT(a, b). SQL Server uses + and also has CONCAT(). CONCAT() is the safest choice if you move between systems. See the MySQL course for the details: string functions in MySQL.
  • Counting characters. Standard SQL calls it CHAR_LENGTH. PostgreSQL and SQLite accept LENGTH for characters, MySQL's LENGTH counts bytes (so use CHAR_LENGTH there for text with accents or other scripts), and SQL Server uses LEN.
  • Cutting text. SUBSTRING(text, start, length) works in MySQL, PostgreSQL, SQL Server and recent SQLite. SUBSTR works in MySQL, PostgreSQL and SQLite, but SQL Server does not have it.
  • Finding text. INSTR exists in MySQL and SQLite. PostgreSQL uses POSITION('@' IN email) or STRPOS. SQL Server uses CHARINDEX('@', email), with the arguments in that order.
  • First or last characters. LEFT and RIGHT exist in MySQL, PostgreSQL and SQL Server but not in SQLite, where you use SUBSTR instead.

Common mistakes

  • Counting positions from 0. SQL strings start at position 1. SUBSTR(name, 0, 3) gives surprising results, and what you get can differ between databases.
  • Forgetting the spaces. first_name || last_name gives MeeraIyer. Add ' ' between the pieces yourself.
  • Using || in MySQL. It is not concatenation there by default, so the query either returns wrong results or fails. Use CONCAT().
  • Ignoring NULL. One empty column makes the whole label NULL. Wrap nullable columns in COALESCE.
  • Expecting functions to edit the table. UPPER in a SELECT only changes the output. Changing stored data takes an UPDATE, which comes later in the course.

Exercise

Write a query that returns each employee in department 1 as a single column called badge, in the form RAO, Arjun (Senior Developer): the last name in capitals, a comma and a space, the first name, then the job title in brackets. Show only department 1, sorted by last name.

Show one possible answer
sql
SELECT UPPER(last_name) || ', ' || first_name || ' (' || job_title || ')' AS badge
FROM employees
WHERE department_id = 1
ORDER BY last_name;
result
+------------------------------------+
|               badge                |
+------------------------------------+
| CHOPRA, Diya (Junior Developer)    |
| DAS, Rohan (Developer)             |
| IYER, Meera (Engineering Director) |
| KAPOOR, Isha (Developer)           |
| NAIR, Kavya (Senior Developer)     |
| RAO, Arjun (Senior Developer)      |
+------------------------------------+

On MySQL, replace the || chain with CONCAT(UPPER(last_name), ', ', first_name, ' (', job_title, ')'). On SQL Server, use + or CONCAT().

Key points

  • Functions take values in and give a value back. They work on every row, and they never change what is stored in the table.
  • Join text with the || operator in standard SQL, PostgreSQL and SQLite, with CONCAT() in MySQL, and with + or CONCAT() in SQL Server.
  • UPPER, LOWER, TRIM, REPLACE and SUBSTR (or SUBSTRING) clean and reshape text. Counting characters is LENGTH in most databases but LEN in SQL Server.
  • If any part of a join is NULL, the || operator gives NULL. Wrap nullable columns in COALESCE when you build labels.

Check yourself

1. What does SELECT UPPER(last_name) FROM employees; do to the stored data?
2. In standard SQL, which expression builds 'Meera Iyer' from first_name and last_name?
3. What does 'Hi, ' || NULL return?
4. You want the part of an email after the @ sign. Which pair of ideas does the portable approach combine?