Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 125 ยท Functions and Expressions

String Functions

Clean, cut and format text in SQL with length, upper, lower, substr, trim, replace, instr, concat and printf, plus the names other databases use for each.

Real data is messy text. Names arrive in random capitalization, emails need to be split into user and domain, product codes need padding, and reports need neatly formatted numbers. String functions let you do all of that inside the query, so the database hands your application text that is already clean.

This lesson walks through the string functions you will use every week in SQLite: measuring text, changing case, cutting pieces out, trimming, replacing, searching and formatting. Every database has the same toolbox, but the names differ, so the end of the lesson has a translation table for PostgreSQL, MySQL and SQL Server.

You already know the concatenation operator || from Calculated Columns. Here you'll combine it with functions to build new values from old ones.

Measuring text with length()

length(text) returns the number of characters in a string. A handy first use is finding the longest values in a column, for example to check whether a label will fit on a screen:

SQL Longest product names ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Insulated Water Bottle has 22 characters, spaces included. Two details matter in practice:

  • length() counts characters, not bytes. length('Lucรญa') is 5 even though the รญ takes two bytes in UTF-8. When you really need bytes (for example, to respect a storage limit), SQLite has octet_length(), which returns 6 for the same word.
  • length(NULL) is NULL, not 0. Like almost every function, a NULL input gives a NULL output. See Working with NULL for why.

Changing case: upper() and lower()

upper() and lower() convert text to capitals or small letters. The most important everyday use isn't cosmetic: it's case-insensitive comparison. The = operator in SQLite is case-sensitive, so 'Lyon' = 'LYON' is false. Lowercasing both sides makes the match reliable:

SQL Case-insensitive search ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

All four customers from Lyon come back, even though the search text was typed in capitals. Applications often lowercase emails and usernames the same way before comparing them.

SQLite's built-in case functions only handle ASCII letters (Aโ€“Z). upper('lucรญa') gives LUCรญA and lower('ร‰COLE') gives ร‰cole: the accented letters are left alone. PostgreSQL, MySQL and SQL Server convert accented letters too. If your data is international and you need full Unicode case folding in SQLite, the ICU extension adds it; otherwise do the conversion in application code.

Cutting out pieces with substr()

substr(text, start, length) returns part of a string. Positions start at 1, not 0, and the length is optional: leave it out to take everything to the end.

Call Result Why
substr('SQLite', 1, 3) SQL three characters starting at position 1
substr('SQLite', 3) Lite from position 3 to the end
substr('SQLite', -4, 2) Li negative start counts from the end
substr('SQLite', 3, 100) Lite asking for too much is fine
substr('SQLite', 10) empty string starting past the end gives ''

SQLite also accepts substring() as another name for the same function, which matches the name most other databases use.

Combined with ||, substr() builds short labels. Here each customer becomes an initial plus a capitalized last name:

SQL Initial and last name ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first row prints Y. GARCIA. Try changing the query to produce Garcia, Y. instead.

Finding text with instr()

instr(text, search) returns the position of the first occurrence of search inside text, or 0 when it isn't there. On its own a position isn't very interesting, but it's exactly what substr() needs to split a value at a separator. Splitting an email at the @ is the classic example:

SQL Split emails at the @ ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

For [email protected] the @ is at position 13, so the user name is the 12 characters before it and the domain is everything after it. The same pattern splits key=value pairs, file names and extensions, or "City, Country" strings.

instr() can also filter rows, since any result above 0 means "found": WHERE instr(name, 'Set') > 0 finds names containing Set. Unlike SQLite's LIKE, instr() is case-sensitive, so it doesn't match set or SET. Wrap the column in lower() when case shouldn't matter. For wildcard patterns, use LIKE instead.

Count occurrences with a replace() trick. SQL has no "count characters" function, but you can compare lengths: length(email) - length(replace(email, '.', '')) is the number of dots in email, because removing them shortens the string by exactly that many characters.

Trimming spaces and other characters

Text typed by people often carries stray spaces. trim() removes them from both ends, ltrim() only from the left (start) and rtrim() only from the right (end). Spaces inside the string are never touched.

Pass a second argument to remove other characters instead of spaces. That argument is a set of characters, not a word: SQLite strips any of those characters, in any order, until it meets one that isn't in the set.

SQL trim, ltrim and rtrim

Press Run (or Ctrl + Enter) to see the result here.

The brackets make the remaining spaces visible. The last column shows the classic mistake: rtrim('BLUE EUR', ' EUR') doesn't remove the word " EUR", it removes every trailing E, U, R and space, so the result is BL. To remove a fixed suffix, cut it off with substr() or use replace().

Cleaning a column is a very common fix-up job. Once you've learned UPDATE, a statement like UPDATE customers SET city = trim(city); repairs a whole table at once.

Replacing text with replace()

replace(text, find, replacement) swaps every occurrence of find. It's case-sensitive, and replacing with an empty string deletes the text:

SQL
SELECT
  replace('2025/03/15', '/', '-'),  -- 2025-03-15
  replace('Banana', 'a', ''),       -- Bnn
  replace('Banana', 'A', 'o');      -- Banana (no capital A to replace)

Functions nest, so you can chain several steps. This query turns product names into URL slugs: lowercase letters with hyphens instead of spaces.

SQL Build URL slugs ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Chef Knife 20 cm becomes chef-knife-20-cm. A production slug function would also remove punctuation and accents, which usually happens in application code, but the idea is the same: each function's output feeds the next one, read from the inside out.

Concatenation and NULL: || versus concat()

The || operator glues strings together, but if any piece is NULL, the whole result is NULL. SQLite (3.44 and later) also has two functions that skip NULL values instead:

  • concat(a, b, ...) joins its arguments and treats NULL as an empty string.
  • concat_ws(separator, a, b, ...) ("with separator") puts the separator between the non-NULL values only.

In the company database, Rania Tanaka has no email address. Compare the three approaches:

SQL NULL-safe concatenation ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

For Rania, with_pipes is NULL, with_concat is just Mail: , and contact is Tanaka, Rania with no dangling comma. concat_ws() is the cleanest way to build full names and addresses from optional parts. The next lessons show another option, COALESCE, which replaces a NULL with a default of your choice.

Formatting with printf() and format()

printf(format, values...) builds a string from a template, just like printf in C or String.format in Java. SQLite also calls it format(). Each % placeholder is replaced by the next value:

Placeholder Meaning Example Result
%s text printf('%s!', 'Hi') Hi!
%d integer printf('%d items', 3) 3 items
%.2f number with 2 decimals printf('%.2f', 3.14159) 3.14
%05d zero-padded to width 5 printf('%05d', 42) 00042
%-8s / %8s left / right aligned in 8 characters printf('[%-6s]', 'ab') [ab ]
%,d thousands separators printf('%,d', 1234567) 1,234,567
%% a literal percent sign printf('%d%%', 15) 15%

The most common jobs are fixed decimals for prices and zero-padded codes:

SQL Formatted codes and prices ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

printf('%.2f', 7.5) gives the text 7.50, which looks right on an invoice, whereas the raw number would print as 7.5. Remember that the result is text: format numbers at the very end, after all the math and sorting is done, or '10.00' will sort before '9.00'.

A few more useful functions

Function What it does Example Result
char(n, ...) characters from Unicode code points char(83, 81, 76) SQL
unicode(text) code point of the first character unicode('รฉ') 233
quote(value) value as an SQL literal quote('it''s') 'it''s'
hex(value) hexadecimal bytes hex('hi') 6869

Extra functions on this site. The SQLite build that runs these examples also includes a common extension with functions like reverse(), padl(), proper() and charindex(). The standard sqlite3 program doesn't have them, so this course doesn't rely on them. Stick to the functions in this lesson and your queries will work in any SQLite.

Padding, repeating and comparing text

Reports and fixed-width files need text of an exact width, banners need a character repeated, and sorting needs a clear idea of which of two strings comes first. Server databases have a function for each job. SQLite doesn't, but printf() and a few tricks cover the same ground.

Padding: LPAD and RPAD

LPAD(text, width, fill) adds fill characters on the left until the text is width long, and RPAD does the same on the right. Both truncate text that is already too long. SQLite's equivalent is printf() with a width: %05d pads a number with zeros, %-12s left-aligns text in 12 characters and %12s right-aligns it. A portable alternative for zero padding is to glue zeros on and keep the last characters with substr():

SQL Padding in SQLite ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The IDs come out as 00001 to 00004, and the | markers show the exact field width of the aligned names. The dotted column fills with dots by cutting from a string of 12 dots: that works for any fill character, but it only pads up to the length of the string you cut from.

Repeating: REPEAT, SPACE and REPLICATE

MySQL and PostgreSQL call it REPEAT('ab', 3), SQL Server calls it REPLICATE('ab', 3), and MySQL and SQL Server also have SPACE(n) for n blanks. In SQLite you can build a string of n copies from zeroblob(n): it is n zero bytes, hex() writes each as 00, and replace() turns every 00 into the text you want:

SQL Repeating text and a text bar chart ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first query returns ***** and ababab. The second pads each name to 22 characters with printf() and appends nine # for the 189.00 tent, seven for the 149.00 speakers, and so on. A zero-length zeroblob() gives an empty string, so products under 20 would simply get no bar.

Comparing text: STRCMP, collations and character codes

Comparing strings means comparing character by character, and the outcome depends on the collation, the rule set for ordering characters. MySQL's STRCMP(a, b) returns โˆ’1, 0 or 1; other databases use the plain operators <, = and >. SQLite compares binary (byte) values by default, so every uppercase letter sorts before every lowercase one, and COLLATE NOCASE ignores case for the ASCII letters. A CASE expression reproduces a STRCMP-style result, and unicode() and char() convert between a character and its code:

SQL Collation and character codes

Press Run (or Ctrl + Enter) to see the result here.

Binary comparison says 'apple' < 'Banana' is false (0), because B has a lower code than a; with NOCASE it becomes true (1). The code of A is 65, and char(97, 98) builds ab.

Reversing, joining, translating and sounding alike

Several more functions exist in server databases only. This table shows the standard name and what you write in SQLite:

Task Server databases SQLite
Reverse a string REVERSE(s) (MySQL, PostgreSQL, SQL Server) recursive CTE, shown below
Join with a separator, skipping NULL CONCAT_WS(sep, a, b, ...) (MySQL, PostgreSQL, SQL Server 2017+) concat_ws(sep, a, b, ...) (3.44+)
Swap characters one-for-one TRANSLATE(s, 'abc', 'xyz') (PostgreSQL, Oracle, SQL Server 2017+) nested replace() calls
Capitalize each word INITCAP(s) (PostgreSQL, Oracle) upper(substr(...)) || lower(substr(...)) per word
Code of a character, character of a code ASCII(c) / CHR(n) (PostgreSQL, Oracle); ASCII / CHAR (SQL Server); ORD / CHAR (MySQL) unicode(c) / char(n)
Sounds-alike code SOUNDEX(s) (MySQL, SQL Server, Oracle; PostgreSQL via the fuzzystrmatch extension) not available

SOUNDEX turns a name into a four-character code (Robert and Rupert both give R163) so that names that sound alike can be matched even when they are spelled differently. It is built for English names and is fuzzy by design: use it to find candidate duplicates, not to decide that two people are the same.

Reversing a string is a good example of thinking in sets. A recursive CTE peels one character off the front of the remaining text and prepends it to the result until nothing is left:

SQL Reverse, translate and join by hand ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first result lists ociN, asoR and fusuY. The second shows the phone number stripped to 5551234567, Maria, and Garcia, Yusuf, Bogota (the NULL is skipped). Chaining four replace() calls is what TRANSLATE('(555) 123-4567', '() -', '') does in a single call in PostgreSQL or Oracle.

|| and + don't always skip NULL. Only CONCAT_WS and the CONCAT family treat NULL as an empty string. In PostgreSQL and SQLite, a || NULL is NULL; Oracle's || happens to treat NULL as empty. Check which operator you are using before you trust a joined label.

The same functions in other databases

The ideas carry over to every database, but the names and some behaviors change. The SQL standard spells several of them with keywords, such as SUBSTRING(name FROM 1 FOR 3) and POSITION('@' IN email); PostgreSQL supports that syntax, SQLite doesn't.

Task SQLite PostgreSQL MySQL SQL Server
Characters in a string length(s) length(s) / char_length(s) char_length(s) (length counts bytes) len(s) (ignores trailing spaces)
Part of a string substr(s, 2, 3) substr / substring substring(s, 2, 3) substring(s, 2, 3)
First / last n characters substr(s, 1, n) / substr(s, -n) left(s, n) / right(s, n) left / right left / right
Find a substring instr(s, 'x') strpos(s, 'x') / position('x' IN s) instr(s, 'x') / locate('x', s) charindex('x', s)
Concatenate ||, concat() ||, concat() concat() (|| means OR by default) +, concat()
Format printf() / format() format() (%s only) / to_char() format(n, 2) format(n, 'N2')
Pad printf('%05d', n) lpad(s, 5, '0') lpad(s, 5, '0') right('00000' + s, 5)
SQL
-- PostgreSQL
SELECT left(email, 5), lpad(id::text, 5, '0'), position('@' IN email)
FROM customers;

-- MySQL
SELECT left(email, 5), lpad(id, 5, '0'), char_length(email)
FROM customers;

-- SQL Server
SELECT left(email, 5), charindex('@', email), len(email)
FROM customers;

Watch out for MySQL's length(): it returns bytes, so length('Lucรญa') is 6 there. Use char_length() when you mean characters.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does substr('database', 5) return?

    Positions start at 1, so position 5 is the b; with no length, substr() takes everything to the end: base.
  2. 2. What does instr('report.final.pdf', '.') return?

    instr() returns the position of the first match. The first dot is the 7th character. It would return 0 only if there were no dot at all.
  3. 3. An employee's email is NULL. What does 'Mail: ' || email return?

    With ||, any NULL piece makes the whole result NULL. concat('Mail: ', email) would return Mail: instead.
  4. 4. Why does rtrim('BLUE EUR', ' EUR') return BL?

    The second argument lists characters to strip, not a word to remove. SQLite keeps removing trailing E, U, R and spaces, which also eats the E and U of BLUE.
  5. 5. Which expression produces INV-00042 from the number 42?

    %05d pads the integer with zeros to a width of 5. %5d would pad with spaces instead.

Summary

  • length() counts characters (octet_length() counts bytes); NULL in gives NULL out.
  • upper() and lower() change case; use them on both sides for case-insensitive comparisons. In SQLite they only convert Aโ€“Z.
  • substr(text, start, length) cuts out a piece with 1-based positions; a negative start counts from the end.
  • instr() finds a substring's position (0 if missing); combine it with substr() to split values at a separator.
  • trim(), ltrim() and rtrim() strip spaces or a set of characters from the ends.
  • replace() swaps every occurrence of a substring, case-sensitively.
  • || returns NULL if any part is NULL; concat() and concat_ws() skip NULL values.
  • printf()/format() pads, aligns and formats numbers, and returns text, so format last.
  • Padding, repeating and comparing: LPAD/RPAD, REPEAT, STRCMP, REVERSE, TRANSLATE, INITCAP and SOUNDEX exist on servers; in SQLite use printf(), zeroblob() with replace(), collations, recursive CTEs and nested replace().
  • Other databases rename many of these (char_length, strpos, charindex, lpad, left/right).

Next: Numeric Functions, where you'll round, truncate and calculate with numbers.