String Handling


String Handling

“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.

Joining Strings together

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.

Comparing strings with each other

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.)

Comparing a String with a Pattern

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.

Fetching part of a string

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.

Find a string or character inside another string

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).

Find the Length of a string

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.

Change the case of a string

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.)

Replace a character (or substring) with another

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.

Trimming White Space from the ends of a string

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.

Combining String Functions

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

 

  • Find out the position (a number) where the space lies in the name. The expression Instr(FullName, ” “) yields this position.
  • The part of the string to the left of the space (this is the above position minus one) yields the first name. The Left function is used here.
  • The part of the string to the right of the space (from the middle of the string onwards, adding one to skip the space itself) yields the last name. The Mid function is used here.

 

Less-Used Functions

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 “*****”

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s