"String” is computer-ese for text. You can use strings in SQL, as well as in a program written in Access’s programming language, Microsoft Visual Basic for Applications (VBA).
In VBA, strings are placed within double quotes, e.g., “John Smith”. In SQL, strings can be defined within either single quotes or double quotes (ANSI/ISO SQL permits only single quotes, Access allows either.)
The empty string (containing zero characters) is indicated by “”.
VBA has a robust set of functions for string handling. Any of these functions
can be used inside a SQL expression. (For that matter, so can any function
defined in VBA, or any function that you write yourself.)
As of 2006, the Access developer documentation still hasn't recovered from the abominations inflicted on it in 2000, when the Office developer documentation - in particular, the table of contents - was almost deliberately and systematically disorganized to make it next to impossible to learn from. (To be able to refer to any part of it usefully, you already needed to be an expert in VBA.) Rather than just provide an alphabetical listing of functions - several online references do that - we describe the kind of things that you might want to do with strings, and then summarize the functions that do this.
To combine two or more strings together, you use the concatenation operator, &. Thus,
"John" & "Smith"
returns
"JohnSmith"
Note the absence of a space between the two words. If you want a space, use
"John" & " " & "Smith"
or simply
"John" & " Smith"
The examples on the rest of this page will use string constants (the expressions within quotes) for simplicity. Remember, however, that what you can do with string constants , you can also do with columns in tables. Thus, in a Patients table, if you have three columns, FirstName, MiddleInitial and LastName, then the SQL statement
SELECT FirstName & " " MiddleInitial & ". " & LastName from Patients
will return the full name of each patient, e.g., James E. Jones, one per row.
The = operator compares two strings for equality. VBA/Access is
case-insensitive for comparisons. Thus, the comparison
"john" = "JOHN"
is True.
One string is "greater" or "less" than the other based on the order they would appear in the dictionary- the greater word would appear after the lesser word. Thus, "Aardvark" is less than "Ball", and "All" is less than "Allspice".
Comparison can also involve special characters - punctuation, spaces and numbers- not normally encountered in dictionary words. Here, the comparison is based on the ASCII code of each character (=American Standard Code for Information Interchange). The smaller the code, the "less" the character. The ASCII code for the space character is 32, that of the numbers 0-9 are 48-57 respectively.
The alphabets also have ASCII codes- A-Z are 65-90, while a-z are 97-122, but as stated earlier, VBA ignores case of alphabets by default. If you do want to perform a case-sensitive comparison between two strings , you use a variant of the function StrComp, as follows:
StrComp("john", "John", vbBinaryCompare)
This function returns 0 if the two strings being compared are equal, -1 if the first string is less than the second, and +1 if the first is greater. (In the example above, you'd get 1, since the ASCII value of lowercase j is greater than that of uppercase J.)
Here, you use the LIKE operator, which takes the general form
string LIKE pattern.
The simplest form of the pattern is one or more letters followed by the symbol
*, which matches any number of characters (including zero characters). (This is
known as a "wildcard", like a Joker in a deck of cards.) For example,
"John Smith" LIKE"Jo*"
returns True. The * can also occur at the beginning or in the middle of the
pattern, or at both ends. Thus
"John Smith" LIKE "*Smith",
"John Smith" LIKE "J*th",
and
"John Smith" LIKE "*Sm*"
would all return true.
The pattern can be much more sophisticated. If the pattern contains the
underscore (_) character, it matches any single character, while with the square
brackets, you can specify a set or range of characters. Thus,
"Smith" LIKE "Sm_th"
and
"Smyth" LIKE "Sm[aeiouy]th"
would both return true, as would
"Smith" LIKE "Smit[e-j]"
The last is true because the letter h is in the range e through j (according to
the dictionary).
The square-brackets pattern can also include negation, by adding the letter ^
as the first character inside the brackets. Thus,
"Smyth" LIKE "Sm[^aeiou]th"
would be true, since y does NOT belong to the set of a, e, i, o and u.
Like the * wildcard, the underscore and bracket wildcards can appear any number of times within a pattern , increasing the power of the matching that you can do.
If you want to check for the characters * and _ themselves as part of a string rather than as wildcards, you place them inside square braces.
The power of LIKE depends a lot on the database that you are using. In Oracle, for example, the square-bracket pattern is not available.
The most versatile function here is Mid, which returns the middle of a string.
Mid("Johnson", 2, 3)
returns "ohn". The parameters to Mid are: the string, the starting position, and the number of characters from this position. Thus, if the number-of-characters parameter is 1, you get the character at a given position.
If you omit the number-of-characters parameter, you get the string from the
middle to the end, thus,
Mid ("James",2)
would return "ames".
Getting the left part of a string: Instead of using
Mid("James", 1, 3)
you can also use
Left("James", 3). (The left three characters)
Right part of a string: The function
Right ("John", 2)
will return "hn" - the rightmost two characters. In practice, you will find that
you will hardly ever use the Right function.
From the left:
Instr("Aardvark", "va")
returns the number 6 (the starting position of the substring "va". If the
substring is not found, this function returns 0. This would be the case if
you tried
Instr("Aardvark", "x")
If the substring occurs multiple times in the string to be compared, the
position of the first occurrence is reported. Thus,
Instr("Aardvark", "r")
will return 3.
From the right (backward)
InstrRev("Aardvark", "r")
will return 7, the position of the last "r".
From the Middle
Instr("Mississippi", "s", 5)
will return 6, the position of the third s. Remember, we are starting the
search from the 5th letter, the position of the second i.
Backwards from the Middle
Instr(5, "Mississippi", "i", 10)
will return 8, the position of the third i; we are starting the search
from the last p.
In practice, I find that I rarely need to use any form of Instr other than the simplest (the first).
len("Johnson")
returns the number 7, the number of characters in the string. Note that
len("John Smith")
would return 10, since spaces are also counted in the characters.
Ucase ("john")
returns "JOHN", while
Lcase ("JohN")
returns "john".
Ucase and Lcase are VBA for Uppercase and Lowercase respectively. (IN standard SQL, the corresponding functions are named Upper and Lower respectively.)
The powerful Replace() function acts like a miniature word-processor. It has several variants.
Replace(target-string, string-to-find, string-to-replace-with)
replaces ALL instances of
string-to-find with string-to-replace-with. For example,
Replace ("banner", "n", "t")
will return "batter".
If the string-to-replace-with is the empty string (""), the
string-to-find will simply be removed, e.g.,
Replace ("catnip","nip","")
would return "cat" .
However, you should note that if you are using a SELECT query in SQL, the original string is not damaged in any way: this function returns a string that shows what will happen if the replacement occurs.
Replacing only part of a string
from a certain position: you specify an additional parameter, the starting
position of the replace. Unfortunately, it returns the sub-string following the
starting position rather than the full string. Thus,
Replace ("banner", "n", "t",4)
would return "ter". If you want "banter" then you must use the expression
Left ("banner",3) & Replace ("banner", "n", "t",4)
which uses two functions and joins them.
Replacing only a certain number of
occurrences: You use two additonal parameters, the third being the number 1,
the last being the number of times you want to replace.
Replace("Mississippi Burning", "i,"x",1,
4)
will return "Mxssxssxppx Burning" - the fifth "i" is
preserved.
Trim (" John Smith ")
returns "John Smith".
If you only want to trim from the front (left) of a string,
Ltrim (" John Smith ")
while to trim from the end (right) of a string,
Rtrim (" John Smith ")
returns " John Smith".
I find that I hardly ever use Rtrim and Ltrim.
The nice thing about functions (of any kind) is that you can combine them. We illustrate with an example. Suppose you have a table Persons containing a FullName column. After inspecting the data, you determine that it contains a first name and a last name separated by a single space.
Suppose you want to extract the first and last names from this column. The following SQL query would do this:
SELECT Left(Fullname, Instr(FullName, " ")-1) as FirstName,
Mid(Fullname, Instr(FullName, " ")+1) as LastName
FROM Persons
Let's dissect the SQL above to see what's going on. The objective is to
I've rarely had to use these functions.
Reversing a string:
StrReverse("abc")
will return "bca"
Repeating a particular character a certain number of times:
String(5,"*")
will return "*****"