Snowflake’s MID function extracts part of a string by position; it is a synonym for SUBSTR (also called SUBSTRING). Use MID(string, start_position, length), such as MID('Snowflake', 5, 5), which returns flake. The starting position is one-based, and you can omit the length to return everything from that position to the end.
What MID Means in Snowflake
MID is a SQL string function for taking a portion of text from a known position. It is useful when a value follows a consistent layout—for example, a code whose first two characters identify a region and whose next four identify a product. The function returns the selected portion as a string; it does not change the original value.
In Snowflake, MID, SUBSTR, and SUBSTRING are alternative names for the same substring operation. That means you can read SQL written with any of the three, and you do not need to learn different position rules for each name. Many SQL users prefer SUBSTR or SUBSTRING because those names are familiar across database systems, while people looking for a “MID” function may recognize the Excel name.
Although the names overlap with spreadsheet functions, Snowflake runs SQL against data, not worksheet cells. You pass a text expression or column name as the first argument, followed by the position and, optionally, the number of characters to return.
Syntax and Arguments
The basic syntax is MID(string_expr, start_expr [, length_expr]). The square brackets indicate that the length argument is optional; do not type the brackets into a query. The first argument is the string to read, the second is where to begin, and the third—if supplied—is the maximum number of characters to return.
For example, with a literal value, SELECT MID('Snowflake', 5, 5) AS part; returns flake. Omitting the length, as in SELECT MID('Snowflake', 5) AS part;, returns flake as well, because the function continues through the end of the input. In a table query, the first argument can be a column, such as MID(product_code, 3, 4).
Start and length can be expressions as well as fixed numbers. This lets a query use a column value as the position or calculate the number of characters. In practice, use integer values for positions and lengths, and make the intended layout clear to anyone who will maintain the SQL.
How Character Positions Work
Snowflake positions are one-based: the first character is position 1, the second is position 2, and so on. This differs from programming languages and APIs that commonly count from zero. For instance, MID('abcd', 2, 2) returns bc, not ab. The length counts the number of characters to return, rather than the ending position.
A start position of 0 is treated as the beginning of the string, so MID('abcd', 0, 2) returns ab. A negative start counts backward from the end. The final character is position -1, the character before it is -2, and so forth. Thus MID('Snowflake', -5, 3) returns fla: the start is the fifth character from the end, and the function takes three characters from there.
For VARCHAR text, positions refer to characters, not UTF-8 bytes. This matters when a string contains non-ASCII text: a character should not be treated as if it always occupies one byte. Also, a symbol that looks like one glyph to a person can be represented by multiple Unicode code points, so a database character position is not necessarily the same thing as a visual cursor position.
Examples for Common String Layouts
Suppose a product identifier always contains a two-character category followed by a four-character item code. If the value is AB1234, extracting the item portion from position 3 gives 1234. A query against a table might look like this:
SELECT product_code, MID(product_code, 3, 4) AS item_code FROM products;
This works because the layout is fixed. It is not a safe way to parse values whose prefixes have different lengths or whose delimiters move. For strings with a separator, such as west-4821, the second field is more naturally expressed with SPLIT_PART: SPLIT_PART('west-4821', '-', 2) returns 4821. That function describes the delimiter-based task directly instead of relying on a hard-coded character offset.
You can also use MID to mask or display a portion of an identifier, but consider what information the output reveals. For example, MID(card_reference, -4) returns the final four characters when the input follows the expected convention. It does not validate the identifier, remove sensitive data from the source, or make a dataset secure by itself; it only creates a substring in the query result.
Empty Strings, NULLs, and Boundary Cases
Boundary behavior is worth checking when values are shorter or less regular than expected. If the start position is beyond the end of the string, the result is an empty string. If the requested length is zero, the result is also empty. A negative length returns an empty string rather than asking Snowflake to read backward. Negative starts are allowed; negative lengths are not a way to count characters from the end.
If an argument is NULL, the result is NULL. That is different from an empty string: NULL represents a missing or unknown value, while an empty string is a value containing no characters. For example, if customer_code is NULL, MID(customer_code, 1, 2) evaluates to NULL. If the column contains an empty string, the result is empty.
In data-cleaning work, do not assume every row has the same length just because a few examples do. Check for NULLs, empty values, short strings, unexpected separators, and leading or trailing spaces. If you need to normalize whitespace, consider applying an appropriate trimming function before extracting a fixed-position segment; otherwise, a stray space can shift every subsequent character.
MID vs. SUBSTR, SUBSTRING, and SPLIT_PART
For Snowflake, MID, SUBSTR, and SUBSTRING are synonyms for positional extraction. Choosing among them is mainly a matter of readability and consistency with the SQL already used in a project. They share the same key behavior: one-based positions, optional length, and support for negative starting positions.
SPLIT_PART is different: it returns a field identified by a delimiter and a part number. Use MID when the location is defined by character offsets, such as “characters 4 through 7.” Use SPLIT_PART when it is defined by a separator, such as “the text after the second hyphen.” If the required pattern is more complex or variable, a regular-expression function may be a better fit than either fixed-position extraction or simple splitting.
Excel’s MID has a similar purpose and also starts counting at 1, but Excel requires a number-of-characters argument. In Snowflake SQL, the length is optional. The environments differ too: Excel formulas refer to cells, whereas Snowflake expressions usually refer to columns, literals, or other SQL expressions. A formula copied between them may therefore need changes even when the function name looks familiar.
Common Mistakes and Practical Tips
- Using zero-based positions: Snowflake starts at 1. Recheck offsets copied from code that counts from 0.
- Confusing length with an ending position: In
MID('abcdef', 2, 3), the length is three, so the result isbcd. - Assuming a fixed layout where none exists: A position-based extraction can return misleading results if prefixes, spaces, or separators vary between rows.
- Expecting a search operation: MID does not find a word or delimiter. It extracts from a specified position. Use a search or split function to locate variable boundaries.
- Treating an empty result as NULL: Check both cases if downstream logic depends on whether a value is missing or simply has no characters.
For recurring transformations, keep the extraction rule understandable and document the source format. If a column’s layout changes, hard-coded offsets may silently produce incorrect-looking values rather than an obvious error. Validate extracted data against representative rows, including malformed or unusually short inputs, before relying on it for reporting or joins.
Frequently Asked Questions
Does Snowflake have a MID function?
Yes. Snowflake supports MID as a synonym for SUBSTR and SUBSTRING. It extracts characters by position from a string expression.
Is Snowflake MID zero-based or one-based?
It is one-based: position 1 is the first character. A start position of 0 is treated as the beginning, and a negative start counts backward from the end.
Can I leave out the length in Snowflake MID?
Yes. When you omit length, Snowflake returns the substring from the specified start position through the end of the input.
What does MID return when the start is past the end?
It returns an empty string. A zero length also produces an empty string; a NULL argument produces NULL instead.
Should I use MID or SPLIT_PART for text after a hyphen?
Use SPLIT_PART when you want a field defined by a delimiter. MID is more suitable when the desired text always occupies a known character position and length.
Related
- Split a Solid Body in SOLIDWORKS: Steps and Tips
- JavaScript String Replace: Syntax, Examples, and Tips
- Snowflake SPLIT to Array: Syntax, Examples, and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Data Filter Multiple Criteria: Practical MethodsTo filter Excel data by multiple criteria, turn on Data > Filter, then set conditions in the column…
- Sort Excel by Date: Steps, Fixes, and Custom OrdersTo sort a worksheet by date in Excel for Microsoft 365 on Windows, select a cell in the…
- How to Round in Excel to 2 Decimal Places CorrectlyTo round a number to two decimal places in Excel, use the ROUND function: if the value is…
- COUNTIF Criteria Examples for ExcelIn Excel for Microsoft 365, COUNTIF counts cells in one range that meet one condition: enter the range…
- XLOOKUP Calculation Examples: Formulas That Work in ExcelXLOOKUP can find a matching value and return a related value, or calculate a result from the matched…
- XLOOKUP in Excel: How to Build and Use the FormulaTo look up a product price by ID, put the ID to find in E2, the product IDs…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.