In Snowflake, use LENGTH(string) to return the number of characters in a string; LEN(string) is an alias. For example, SELECT LENGTH('Snowflake'); returns 9. The function counts spaces too, returns NULL for a NULL input, and measures bytes instead when its input is binary.
What the Snowflake LENGTH function does
LENGTH is a built-in SQL function for measuring a string’s character count. It is useful when you need to inspect text, check a value against a character limit, or create a condition based on how much text a column contains. The result is a number, so you can display it, compare it, or use it in another expression.
For example, SELECT LENGTH('Snowflake'); returns 9, one for each letter. The equivalent expression SELECT LEN('Snowflake'); returns the same result. In Snowflake, LEN is a synonym for LENGTH; using LENGTH is often clearer to readers because it states what is being measured.
The function does not change, shorten, or otherwise edit its input. It only reports a length. To remove text, use a separate operation such as SUBSTR, and to remove surrounding spaces, use a trimming function.
Basic syntax and examples
The basic syntax is LENGTH(expression), where the expression evaluates to a string or binary value. It can be a quoted string, a table column, or an expression that produces text. These examples show the basic behavior:
SELECT LENGTH('cat');returns3.SELECT LENGTH('');returns0for an empty string.SELECT LENGTH('a ');returns3; the two spaces at the end count.SELECT LEN('hello');returns5, just likeLENGTH.
On a table, you can select the value and its measured length side by side. For example, with a table named customers and a text column named display_name, use SELECT display_name, LENGTH(display_name) AS character_count FROM customers;. Each row gets its own count. The alias character_count is just a label for the returned column; it does not alter the stored name.
How spaces, empty strings, and NULL behave
Leading, internal, and trailing spaces are characters and contribute to the result. For instance, LENGTH(' hi ') is 5: two spaces, two letters, and one final space. This often explains why a length check returns a larger number than someone expects when data has been padded or copied from another system.
An empty string and a missing value are not the same thing. An empty string, written as '', has a length of zero. A NULL value means the value is unknown or absent, and LENGTH(NULL) returns NULL, not zero. In a comparison such as WHERE LENGTH(display_name) = 0, rows with a null name do not match. Handle those separately if the application considers both blank and missing values invalid.
You can replace a null length with zero when that is appropriate, for example with COALESCE(LENGTH(display_name), 0). Be aware that this intentionally treats “no value” and “a value containing zero characters” alike in the result. If those cases matter differently, retain a separate null check, such as display_name IS NULL.
Characters are not always bytes or visible symbols
For string input, LENGTH counts characters, not the number of bytes needed to encode the text. A basic Latin letter generally uses one byte in UTF-8, while many accented characters and symbols use more. For example, in Snowflake, SELECT LENGTH('é'), OCTET_LENGTH('é'); returns 1 for the character count and 2 for the UTF-8 byte count. The second value is useful when checking an encoded payload or a byte-based limit.
Unicode also distinguishes a code point from a user-perceived character. A letter with an accent can be represented as a single precomposed character or as a base letter followed by a combining accent. Those forms can look alike on screen but contain different numbers of code points, so their character lengths may differ. Similarly, some emoji displayed as one symbol are made from multiple Unicode code points joined together. A length count is therefore not always a count of what a person sees as separate symbols.
When the requirement concerns bytes, use OCTET_LENGTH for a string rather than assuming its character count is sufficient. When the requirement concerns visible graphemes, such as user-perceived characters in a multilingual interface, confirm that the database’s character-counting behavior matches the application’s definition. A simple length check is not necessarily a complete substitute for locale-aware text validation.
String length versus binary length
Snowflake supports binary values as well as text. If LENGTH receives a binary expression, it returns the number of bytes in that binary value. That differs from the string case, where the result is a character count. The input type therefore matters: two visually similar values can produce different interpretations depending on whether the expression is text or binary.
For strings, use LENGTH when you need characters and OCTET_LENGTH when you need bytes. For binary data, LENGTH itself reports the byte count. If a value arrives in a semi-structured column or through a conversion, check its type rather than relying on how it is displayed. When the type is not already a string, an explicit cast may make the intended measurement easier to understand.
This distinction matters in integrations and file-handling workflows. A system may specify a maximum number of bytes for a message field, while a user-facing rule may specify a maximum number of characters for a name. Those are different constraints, particularly for text containing non-ASCII characters. Apply the measure named by the receiving system’s specification.
Using LENGTH for validation and filtering
A common use is finding values outside an expected range. For example, with a table called accounts and a text column called account_code, SELECT account_code, LENGTH(account_code) AS n FROM accounts WHERE LENGTH(account_code) <> 8; returns codes whose character count is not eight. This can help identify malformed records, but it does not establish that the characters themselves are valid. A code could have eight characters and still contain punctuation or letters where digits are required.
Length can also help summarize data. For example, selecting MIN(LENGTH(description)) and MAX(LENGTH(description)) can show the shortest and longest non-null descriptions in a group. Since aggregate functions generally ignore null inputs, keep null counts separate if missing descriptions are important to the analysis. To see the distribution rather than only its extremes, group by the computed length and count rows in each group.
For a maximum-length rule, define what happens to nulls and whitespace before writing the condition. Should a string containing only spaces count as empty? Should spaces at the ends be accepted? Should a missing value pass? These are business rules, not decisions made automatically by LENGTH. Express them explicitly so the query’s behavior is clear.
Trimming text before measuring it
If the goal is to measure a value after removing ordinary spaces at its ends, combine LENGTH with TRIM: LENGTH(TRIM(display_name)). To ignore only trailing spaces, use LENGTH(RTRIM(display_name)). For example, SELECT LENGTH(' cat '), LENGTH(TRIM(' cat ')); returns 7 and 3, respectively. The first expression measures the original text; the second measures the trimmed result.
Trimming is not the same as removing every space. An internal space in Mary Jane remains when the ends are trimmed, so its length is still ten. Also, trimming changes the text being measured for that expression; it does not update the table. If cleanup is required in stored data, perform an explicit update only after confirming the desired transformation and its effect on the original values.
Common mistakes and useful checks
- Expecting a byte count: String
LENGTHcounts characters. UseOCTET_LENGTHwhen the limit is in bytes. - Assuming spaces are ignored: Spaces count unless the expression trims them first.
- Treating NULL as zero: A null input produces a null result. Use
COALESCEonly when substituting zero is intended. - Assuming a length proves validity: Length does not check allowed characters, formatting, or whether a value is meaningful.
- Counting what appears on screen: A displayed symbol can consist of more than one Unicode code point.
For an unexpected result, inspect the value with a visible delimiter around it, and check both the type and the byte count. A query such as SELECT '[' || display_name || ']' AS shown_value, LENGTH(display_name), OCTET_LENGTH(display_name) FROM customers; can make leading or trailing spaces easier to notice. Concatenation with a null can itself yield a null result, so use a null-safe display expression if the column may be null.
Frequently Asked Questions
What is the Snowflake function for string length?
Use LENGTH(string). It returns the number of characters for string input. LEN(string) is an alias and returns the same result.
Does Snowflake LENGTH count spaces?
Yes. Leading, internal, and trailing spaces count as characters. Apply TRIM or RTRIM inside the expression if the rule says to exclude spaces at the ends.
What does LENGTH return for NULL?
It returns NULL. An empty string returns zero, so a null value and an empty string need separate handling if they mean different things in your data.
How do I count bytes instead of characters?
For a string, use OCTET_LENGTH(string). For a binary value, LENGTH(binary_value) returns its number of bytes. Choose based on whether the constraint is expressed in characters or encoded bytes.
Does LENGTH remove or truncate text?
No. It only reports a count. Use a separate function or data-modification operation to change text, and check the exact requirement before trimming or truncating values.
Related
- Print String and Int on Arduino
- Arduino String Data Type: How It Works and When to Use It
- Snowflake SPLIT: Turn a String Into an Array
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Concatenate in Excel and Keep Leading ZerosTo concatenate values in Excel and keep leading zeros, format the value with TEXT inside the formula before…
- Excel Rule for Dates Before Today: Formula and SetupTo highlight dates before today in Excel for Microsoft 365 on Windows, select the date cells and create…
- SQL Functions in Snowflake: Types and ExamplesSQL functions in Snowflake are built-in or user-defined operations that take values as input and return a value…
- Excel Payment Tracker: Setup, Formulas, and Practical TipsAn Excel payment tracker is a worksheet for recording what is owed, what has been paid, when payments…
- Excel Concatenate String Examples: Formulas That WorkTo concatenate strings in Excel, join cell references and text with the ampersand operator (&), or use CONCAT…
- How to Merge Two Columns in Excel With Text Using a FormulaTo combine text from two Excel columns, enter a formula such as =A2&" "&B2 in a new column,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.