Split() and Concat With & Function in MS Access

1. 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 on the basis of the space.

Syntax :

Split(expression, delimiter, limit, compare)

Parameters :

• Expression – String to split into substrings based on a delimiter.
• Delimiter – Optional. The delimiter used to split expression into substrings.
• Limit – Optional. The maximum number of substrings split from expression.
• Compare – Optional. For Binary comparison value will be pass 0, and for Textual comparison value will be pass 1.

Returns : It will return string with the separator.

Example-1 :

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

Output :

SplitString
{“Geeks”, “For”, “Geeks”}

Example-2 :

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

Output :

SplitString
{“geeks”, “for”, “geeks”}

Example-3 :

SELECT Split("The best computer science portal")

Output :

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

2. Concat With & :
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

Example-1 :

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

Output :

The_String
GEEKSFORGEEKS

Example-2 :

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

Output :

"ABC"

Example-3 :

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

Output :

The_String
COMPUTER SCIENCE