Split() and Concat With & Function in MS Access

In MS Access, the Split() and Concat With & functions are used to manipulate and combine strings. The Split() function is used to split a string into multiple parts based on a specified delimiter, while the Concat With & function is used to concatenate multiple strings together.

MS Access Split() Function

In MS Access, the Split() function splits the string into an array of strings. In this function, we will pass the string and the separator by which the function will separate the string. If we pass no separator then it will separate the string based on the space.

Syntax

Split(expression, delimiter, limit, compare)

Parameters:

1. Expression: String to split into substrings based on a delimiter.
2. Delimiter (Optional): The delimiter is used to split the expression into substrings.
3. Limit (Optional): The maximum number of substrings split from the expression.
4. Compare (Optional): For Binary comparison value will be pass 0, and for Textual comparison value will be pass 1.

Return Value:

• It will return string with the separator.

MS Access Split() function Examples

Let's look at some examples of the split() function in MS Access

Example 1: Basic Split() Function with Default Delimiter

SELECT Split("Geeks For Geeks") AS SplitString;

Output:

Example 2: Split() Function with Custom Delimiter

SELECT Split("geeks:for:geeks", ":") AS SplitString;

Output :

Example 3: Split() Function with Default Space Delimiter

SELECT Split("The best computer science portal")

Output :

{"The", "best", "computer", "science", "portal"}

MS Access Concat With & Function

In MS Access, we can combine two or more than two strings with the help of & into one string. It will make all strings into one string. We can perform concatenation of the multiple strings together into a single string with the & operator.

Concatenation can also be performed between the string with the help of the Concat function. There is no parameters or arguments for the & operator.

Syntax

string1 & string2 & string3 & string_n

MS Access Concat With & Function Examples

Let's look at some examples of the Concat With & Function in MS Access.

Example 1: Basic Concatenation

SELECT "GEEKS" & "FOR" & "GEEKS" AS The_String;

Output:

Example 2: Concatenation with NULL Handling

SELECT "A" & NULL & "B"& NULL & "C";

Output:

"ABC"

Example 3: Concatenation with Spaces

SELECT "COMPUTER" & " " & "SCIENCE"  AS The_String;

Output: