TO_UTF8() / FROM_UTF8()

A VARCHAR holds characters. Anything written to disk or sent over a network is bytes. UTF-8 is the rule that maps each Unicode character to between 1 and 4 bytes, and every byte conversion in this lesson has passed through it: TO_UTF8(text) returns the UTF-8 bytes of a string as VARBINARY, and FROM_UTF8(bytes) reads them back. Usually the 2 sides agree. The places where they part ways are where pipelines break.

The first byte announces the length. A byte from 00 to 7F is a whole character, which is why plain ASCII text has identical bytes in UTF-8. C2 to DF starts a 2-byte character, E0 to EF a 3-byte one, F0 to F4 a 4-byte one, and 80 to BF can only continue a character already started. A decoder uses those ranges to find boundaries, and to notice bytes that break the rules.

LENGTH counts characters, TO_UTF8 exposes bytes

LENGTH on a VARCHAR counts characters. LENGTH on VARBINARY counts bytes, so LENGTH(TO_UTF8(name)) is a string's size on the wire. For English names the 2 numbers match, which hides the difference until the data reaches a market with accents or another script.

SELECT
  display_name,
  LENGTH(display_name) AS chars,
  LENGTH(TO_UTF8(display_name)) AS bytes
FROM merchants

VARCHAR(n) limits characters, so all 4 names fit a VARCHAR(16) column here. A partner file format that caps the field at 16 bytes rejects 2 of them. Check exports against the receiver's unit: WHERE LENGTH(TO_UTF8(display_name)) > 16 lists the rows that will bounce.

• 3x

Cutting bytes through the middle of a character

Trimming to a byte cap with SUBSTR on VARBINARY counts bytes, and the cut can land inside a character. The query below shows 'Café' as bytes and cuts it at 4: the bytes 43 61 66 C3 A9 lose A9, which leaves C3, a lead byte whose partner is gone. Cutting the VARCHAR at 4 counts characters instead and keeps the é whole.

SELECT
  TO_HEX(TO_UTF8('Café')) AS all_bytes,
  TO_HEX(SUBSTR(TO_UTF8('Café'), 1, 4)) AS first_4_bytes,
  SUBSTR('Café', 1, 4) AS first_4_chars

Decoding those 4 bytes is where the damage shows. FROM_UTF8 replaces any invalid sequence with U+FFFD, the replacement character drawn as �, so FROM_UTF8(SUBSTR(TO_UTF8('Café'), 1, 4)) returns 'Caf�'. A second argument sets a different replacement, and an empty string drops the bad bytes: FROM_UTF8(SUBSTR(TO_UTF8('Café'), 1, 4), '') returns 'Caf'. To trim safely, cut the VARCHAR with SUBSTR and check its byte length afterwards.

Bytes that were never UTF-8

Older systems often write Latin-1, where é is the single byte E9. To a UTF-8 reader, E9 announces a 3-byte character, and nothing after it continues one, so the decode produces �. An import table holds each name's raw bytes as hex: row 1 is 436166C3A9, row 2 is 436166E9 from a Latin-1 source, and row 3 is 4B6169. Comparing a default decode with one that marks bad bytes as ? flags the broken rows, because the 2 versions differ only where invalid bytes were replaced.

SELECT
  row_id,
  FROM_UTF8(FROM_HEX(name_hex)) AS name_default,
  FROM_UTF8(FROM_HEX(name_hex), '?') AS name_marked,
  FROM_UTF8(FROM_HEX(name_hex)) != FROM_UTF8(FROM_HEX(name_hex), '?') AS has_bad_bytes
FROM raw_imports

A price tag column holds '€5'. What does this return?

SELECT
  LENGTH(TO_UTF8('€5')) AS tag_bytes

• It returns 2, 1 for each character in the string
• It returns 3, since € takes 2 bytes the way é does
• It returns 4: € takes 3 bytes and the digit 5 takes 1
• It returns 8, since UTF-8 gives every character 4 bytes

Against a 16-byte cap, the 2 filters disagree on the merchants table. LENGTH(display_name) > 16 returns no rows, since the longest name runs 15 characters. LENGTH(TO_UTF8(display_name)) > 16 returns Pizzeria 🍕 Roma and 東京ラーメン at 18 bytes each, the 2 rows the partner would reject.

About this lesson

This section is part of the String Functions: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.