Text functions and Unicode

Text functions measure, cut, search, change and join strings: length(), substr(), instr(), replace(), upper(), || and concat() cover most daily work. All three engines of this track have them, often under other names, and they trip over the same two things. The first is NULL: one missing value inside || makes the whole result NULL. The second is text beyond English letters, where one written letter can be several Unicode code points and each code point several bytes, so a Hindi word has three different lengths depending on who counts. This lesson runs each function on real names, including Devanagari, and compares the engines in a table at the end.

Each query here runs in your browser with SQLite 3.49.1, from sql.js 1.14.2, Devanagari included; what this lesson says about PostgreSQL 18 and MySQL 8.4 rests on their documentation.

Length: characters and bytes

length() counts the characters of a text value, where a character means a Unicode code point, and octet_length() counts the bytes the text takes in UTF-8:

-- length() counts characters (Unicode code points); octet_length() counts the bytes of their UTF-8 encoding.
CREATE TABLE words (word TEXT);
INSERT INTO words VALUES ('Asha'), ('café'), ('₹500'), ('தமிழ்'), ('नमस्ते');

SELECT length(word) AS characters, octet_length(word) AS bytes, word
FROM words;

┌────────────┬───────┬────────┐
│ characters │ bytes │  word  │
├────────────┼───────┼────────┤
│ 4          │ 4     │ Asha   │
│ 4          │ 5     │ café   │
│ 4          │ 6     │ ₹500   │
│ 5          │ 15    │ தமிழ்  │
│ 6          │ 18    │ नमस्ते │
└────────────┴───────┴────────┘

English letters take one byte each, so Asha is 4 and 4. The é of café, the rupee sign and every Tamil and Devanagari code point take more: नमस्ते is 6 characters and 18 bytes. A reader, though, sees neither number:

The current Unicode rules for what a reader perceives as one letter, called grapheme clusters in Unicode Standard Annex #29, group नमस्ते into 3, because स्ते joins two consonants and a vowel sign. SQL functions count code points, and this example lists them:

-- The six code points of one Hindi word, one row each. A row's text is a single code point, so the
-- virama (U+094D) and the vowel sign (U+0947) appear on their own, without the letter they belong to.
CREATE TABLE positions (i INTEGER);
INSERT INTO positions VALUES (1), (2), (3), (4), (5), (6);

SELECT i,
       printf('U+%04X', unicode(substr('नमस्ते', i, 1))) AS code_point,
       octet_length(substr('नमस्ते', i, 1))               AS bytes,
       substr('नमस्ते', i, 1)                             AS text
FROM positions;

-- Cutting after four code points leaves the virama dangling: the result is not a whole word.
SELECT substr('नमस्ते', 1, 3) AS first_three, substr('नमस्ते', 1, 4) AS first_four;

┌───┬────────────┬───────┬──────┐
│ i │ code_point │ bytes │ text │
├───┼────────────┼───────┼──────┤
│ 1 │ U+0928     │ 3     │ न    │
│ 2 │ U+092E     │ 3     │ म    │
│ 3 │ U+0938     │ 3     │ स    │
│ 4 │ U+094D     │ 3     │ ्    │
│ 5 │ U+0924     │ 3     │ त    │
│ 6 │ U+0947     │ 3     │ े    │
└───┴────────────┴───────┴──────┘
┌─────────────┬────────────┐
│ first_three │ first_four │
├─────────────┼────────────┤
│ नमस         │ नमस्       │
└─────────────┴────────────┘

The fourth code point, U+094D, is the virama, a sign that joins the consonant before it to the next one. Taken alone, it and the vowel sign U+0947 show up as marks without a letter. That is also why substr() can damage a word: four code points from the start end in a dangling virama, which is not a word anyone wrote. Cut text for display only at spaces, or let the application cut it with a library that knows grapheme clusters.

The engines also disagree about the word “length”. PostgreSQL’s length() and char_length() count characters, as SQLite’s length() does. MySQL’s LENGTH() counts bytes, so it returns 18 for नमस्ते, while its CHAR_LENGTH() returns 6. A check such as LENGTH(name) <= 50 therefore allows only 16 Devanagari characters in MySQL.

Taking parts of a string

-- Parts of a string: substr() counts from the start, or from the end with a negative position.
SELECT substr('Bengaluru', 1, 4)  AS first_four,
       substr('Bengaluru', -4)    AS last_four,
       substr('Bengaluru', 5)     AS from_fifth,
       instr('Bengaluru', 'luru') AS found_at,
       instr('Bengaluru', 'Luru') AS other_case;

┌────────────┬───────────┬────────────┬──────────┬────────────┐
│ first_four │ last_four │ from_fifth │ found_at │ other_case │
├────────────┼───────────┼────────────┼──────────┼────────────┤
│ Beng       │ luru      │ aluru      │ 6        │ 0          │
└────────────┴───────────┴────────────┴──────────┴────────────┘

substr(text, start, count) counts positions from 1. Without a count it runs to the end, and a negative start counts from the end, so substr('Bengaluru', -4) is luru. instr(text, part) returns the position of the first match, or 0 when there is none, and it respects case, which is why 'Luru' is not found.

PostgreSQL and MySQL spell these differently. Both have right(text, n) and left(text, n) for the ends of a string. PostgreSQL finds text with strpos(text, part) or position(part IN text). MySQL has LOCATE(part, text) and INSTR(text, part), with the arguments in opposite orders, an easy pair to mix up when moving queries between engines. To take the second field of a comma-separated value, PostgreSQL has split_part(s, ',', 2), MySQL nests SUBSTRING_INDEX() twice, SUBSTRING_INDEX(SUBSTRING_INDEX(s, ',', 2), ',', -1), and SQLite needs instr() to find the commas and substr() to cut between them.

Cleaning text and changing case

-- Trimming, replacing and changing case. SQLite's upper() and lower() change only ASCII letters.
SELECT trim('  Pune  ')               AS trimmed,
       ltrim('000452', '0')            AS no_zeros,
       replace('98765-43210', '-', '') AS digits,
       upper('Asha Rao')               AS ascii,
       upper('straße, café')           AS other;

┌─────────┬──────────┬────────────┬──────────┬──────────────┐
│ trimmed │ no_zeros │   digits   │  ascii   │    other     │
├─────────┼──────────┼────────────┼──────────┼──────────────┤
│ Pune    │ 452      │ 9876543210 │ ASHA RAO │ STRAßE, CAFé │
└─────────┴──────────┴────────────┴──────────┴──────────────┘

trim() removes spaces from both ends, and with a second argument it removes those characters instead, so ltrim('000452', '0') drops leading zeros. replace() swaps every occurrence of one string for another, here deleting the hyphen in a phone number. The last column shows a SQLite limit: upper() and lower() change only the 26 ASCII letters, so ß and é stay as they were. PostgreSQL and MySQL convert other letters too, following the database’s locale in PostgreSQL and the character set in MySQL. Devanagari and Tamil have no upper and lower case, so these functions leave them alone in every engine.

Joining text, and NULL

|| joins two strings in PostgreSQL and SQLite. Like arithmetic, it returns NULL when either side is NULL:

-- Joining name parts when the middle name can be missing.
CREATE TABLE people (given TEXT, middle TEXT, surname TEXT);
INSERT INTO people VALUES ('Asha', NULL, 'Rao'), ('Vikram', 'Singh', 'Rathore');

SELECT given || ' ' || middle || ' ' || surname AS pipes,
       concat(given, ' ', middle, ' ', surname)  AS with_concat,
       concat_ws(' ', given, middle, surname)    AS with_concat_ws
FROM people;

┌──────────────────────┬──────────────────────┬──────────────────────┐
│        pipes         │     with_concat      │    with_concat_ws    │
├──────────────────────┼──────────────────────┼──────────────────────┤
│                      │ Asha  Rao            │ Asha Rao             │
│ Vikram Singh Rathore │ Vikram Singh Rathore │ Vikram Singh Rathore │
└──────────────────────┴──────────────────────┴──────────────────────┘

Asha has no middle name, so the || version of her full name is NULL (the empty cell). concat() treats NULL as an empty string, which keeps her name but leaves two spaces between the parts. concat_ws(), “concatenate with separator”, takes the separator first and skips NULL values entirely, so it gives Asha Rao. When an empty string should count as missing too, turn it into NULL first with nullif(middle, '').

Padding, cutting and masking with printf

SQLite has no lpad() or repeat(), but printf() covers both and more. Its widths and precisions count bytes, unless you add the ! flag, which makes them count characters:

-- printf() measures widths and precisions in bytes, or in characters with the ! flag.
CREATE TABLE formats (how TEXT, pattern TEXT);
INSERT INTO formats VALUES
  ('pad to 8 bytes',      '[%-8s]'),
  ('pad to 8 characters', '[%-!8s]'),
  ('first 3 bytes',       '%.3s'),
  ('first 3 characters',  '%!.3s');

SELECT how, printf(pattern, 'Asha') AS latin, printf(pattern, 'नमस्ते') AS hindi
FROM formats;

┌─────────────────────┬────────────┬────────────┐
│         how         │   latin    │   hindi    │
├─────────────────────┼────────────┼────────────┤
│ pad to 8 bytes      │ [Asha    ] │ [नमस्ते]   │
│ pad to 8 characters │ [Asha    ] │ [नमस्ते  ] │
│ first 3 bytes       │ Ash        │ न          │
│ first 3 characters  │ Ash        │ नमस        │
└─────────────────────┴────────────┴────────────┘

[%-8s] pads Asha to 8 bytes, but नमस्ते already has 18 bytes, so it gets no padding at all, and %.3s keeps just three bytes, one Devanagari code point. With ! both count characters. When you align columns of names in a report, use the ! form, or pad in the application. PostgreSQL and MySQL pad with lpad() and rpad(), which count characters.

Two more printf() tricks build masks. %.*c repeats a character as many times as the argument before it says, and %06d pads a number with zeros to six digits. Here the local part of an email address is hidden:

-- Hiding part of an email address: keep the first letter and the domain.
CREATE TABLE contacts (email TEXT);
INSERT INTO contacts VALUES ('asha.rao@example.com'), ('vk@example.in'), (NULL);

SELECT email,
       substr(email, 1, 1)
         || printf('%.*c', instr(email, '@') - 2, '*')
         || substr(email, instr(email, '@')) AS masked
FROM contacts;

┌──────────────────────┬──────────────────────┐
│        email         │        masked        │
├──────────────────────┼──────────────────────┤
│ asha.rao@example.com │ a*******@example.com │
│ vk@example.in        │ v*@example.in        │
│                      │                      │
└──────────────────────┴──────────────────────┘

instr(email, '@') finds the at sign, the stars replace everything between the first letter and it, and NULL stays NULL, because any || with NULL is NULL. A masked value in a report is not protection on its own: whoever can query the table still sees the original.

Text that looks the same but is not

Unicode sometimes offers two ways to write one letter. The Hindi letter ज़ exists as a single code point, and also as ज followed by the nukta sign, and keyboards and apps produce both:

-- Two ways to write the letter ज़: one code point, or ज followed by the nukta sign.
SELECT char(2395) = char(2332, 2364) AS equal,
       length(char(2395))            AS length_one,
       length(char(2332, 2364))      AS length_two,
       char(2395)                    AS one_code_point,
       char(2332, 2364)              AS two_code_points;

┌───────┬────────────┬────────────┬────────────────┬─────────────────┐
│ equal │ length_one │ length_two │ one_code_point │ two_code_points │
├───────┼────────────┼────────────┼────────────────┼─────────────────┤
│ 0     │ 1          │ 2          │ ज़              │ ज़              │
└───────┴────────────┴────────────┴────────────────┴─────────────────┘

The two strings look identical, yet = says they differ, and their lengths are 1 and 2. A search for one form misses rows stored in the other. Unicode defines normalization forms that settle on one spelling, in Unicode Standard Annex #15; PostgreSQL applies them with normalize(text, NFC), and the NFC form of both strings here is the same two-code-point sequence. SQLite has no normalization function, so normalize text in the application before you store or compare it.

Formatting numbers for people

printf('%,d', n) adds a comma before every third digit from the right, the international style. Indian reports group the digits differently, and a CASE on the size of the number builds that style from integer arithmetic:

-- Digit grouping for display: in thousands, and in the Indian style (groups of two after the first three).
CREATE TABLE totals (rupees INTEGER);
INSERT INTO totals VALUES (999), (45000), (1234567), (98765432), (9876543210);

SELECT rupees,
       printf('%,d', rupees) AS thousands,
       CASE
         WHEN rupees < 1000     THEN printf('%d', rupees)
         WHEN rupees < 100000   THEN printf('%d,%03d', rupees / 1000, rupees % 1000)
         WHEN rupees < 10000000 THEN printf('%d,%02d,%03d', rupees / 100000, rupees / 1000 % 100, rupees % 1000)
         ELSE printf('%d,%02d,%02d,%03d', rupees / 10000000, rupees / 100000 % 100, rupees / 1000 % 100, rupees % 1000)
       END AS indian
FROM totals;

┌────────────┬───────────────┬───────────────┐
│   rupees   │   thousands   │    indian     │
├────────────┼───────────────┼───────────────┤
│ 999        │ 999           │ 999           │
│ 45000      │ 45,000        │ 45,000        │
│ 1234567    │ 1,234,567     │ 12,34,567     │
│ 98765432   │ 98,765,432    │ 9,87,65,432   │
│ 9876543210 │ 9,876,543,210 │ 987,65,43,210 │
└────────────┴───────────────┴───────────────┘

How the engines differ

Your turn

-- display_name: 'Surname, Given' (the given name alone without a surname);
-- masked_phone: every character but the last four replaced by '*'.
SELECT id,
       given_name AS display_name,
       phone AS masked_phone
FROM customers;

# Sample tests: each one builds a small customers table, runs your query on it and compares the rows it returns (in
# any order).
tests:
  - name: shows surname first and masks all but the last four characters
    setup: |
      CREATE TABLE customers (id INTEGER PRIMARY KEY, given_name TEXT NOT NULL, surname TEXT, phone TEXT);
      INSERT INTO customers VALUES (1, 'Asha', 'Rao', '9876543210'), (2, 'Vikram', 'Rathore', '+919812345678');
    expected:
      columns: [id, display_name, masked_phone]
      rows:
        - [1, 'Rao, Asha', '******3210']
        - [2, 'Rathore, Vikram', '*********5678']
  - name: handles a missing surname, a missing phone and a short phone
    setup: |
      CREATE TABLE customers (id INTEGER PRIMARY KEY, given_name TEXT NOT NULL, surname TEXT, phone TEXT);
      INSERT INTO customers VALUES (1, 'Bilal', NULL, '9123456780'), (2, 'Meena', 'Iyer', NULL), (3, 'Joseph', 'Dsouza', '1234');
    expected:
      columns: [id, display_name, masked_phone]
      rows:
        - [1, 'Bilal', '******6780']
        - [2, 'Iyer, Meena', null]
        - [3, 'Dsouza, Joseph', '1234']
  - name: works for names in Devanagari
    setup: |
      CREATE TABLE customers (id INTEGER PRIMARY KEY, given_name TEXT NOT NULL, surname TEXT, phone TEXT);
      INSERT INTO customers VALUES (1, 'प्रिया', 'शर्मा', '9988776655'), (2, 'अर्जुन', NULL, NULL);
    expected:
      columns: [id, display_name, masked_phone]
      rows:
        - [1, 'शर्मा, प्रिया', '******6655']
        - [2, 'अर्जुन', null]

Interview questions

Fresher · Why can LENGTH() return different numbers for the same Hindi word in MySQL and PostgreSQL?

Because the two functions count different things. MySQL’s LENGTH() returns the number of bytes, and in UTF-8 every Devanagari code point takes 3 bytes, so नमस्ते gives 18. PostgreSQL’s length() counts characters, meaning Unicode code points, and gives 6; MySQL’s equivalent is CHAR_LENGTH(), and both engines have octet_length() for bytes. A reader sees yet another number, 3 letters, because one written letter can combine several code points. The difference matters for validation and limits: a rule written with MySQL’s LENGTH() silently allows a third as many Hindi characters as English ones. Use the character count for user-facing limits, the byte count for storage, and cut text at spaces rather than at a position.

Key takeaways

• length() counts code points and octet_length() bytes; a Devanagari code point takes 3 bytes, and one written letter can be several code points.
• MySQL’s LENGTH() counts bytes; use CHAR_LENGTH() for characters.
• substr() counts from 1, accepts a negative start in SQLite, and can cut a written letter in two.
• || returns NULL if any part is NULL; concat_ws() skips NULLs in every engine, and in MySQL || means OR unless PIPES_AS_CONCAT is set.
• SQLite’s upper() and lower() change ASCII letters only, and its printf() counts bytes unless you add !.
• Text that looks the same can differ in code points; normalize it before storing or comparing.