MS Access Split() Function
The Split() function in MS Access breaks a single string into an array (list) of smaller strings. This is incredibly useful for processing text data that's structured with delimiters.
Split(): Definition and Usage
Imagine you have a string like "apple,banana,orange". Split() lets you easily separate this into individual items ("apple", "banana", "orange") based on the comma as a separator. You can control how many items are returned and the type of string comparison used.
Syntax
Syntax
Split(string, separator, limit, compare)
Parameter Values
| Parameter | Description |
|---|---|
string |
The string you want to split. This is required. |
separator (Optional) |
The character(s) used to separate the parts of the string. If omitted, a space is used. |
limit (Optional) |
The maximum number of substrings to return. The default (-1) returns all substrings. |
compare (Optional) |
Specifies the type of comparison for the separator:
|
Examples
Splitting a String with Spaces
Splitting a string using the default space separator.
Syntax
SELECT Split("SQL Tutorial is fun!") AS SplitString;
Output
{"SQL", "Tutorial", "is", "fun!"}
Splitting a String with a Custom Separator and Limit
This shows splitting with a colon (:) as the separator and limiting the results to only the first two parts.
Syntax
SELECT Split("red:green:yellow:blue", ":", 2) AS SplitString;
Output
{"red", "green"}