databasedev.co.uk - database solutions and downloads for microsoft access

Introduction

When writing our SQL queries in Access there may be times when we want to manipulate the data that is contained in the columns rather than just returning the whole column value. In this article the guys from SQL Book outline some useful functions that can be used in our SQL queries to manipulate string values and give some real world examples of them in use.

To use these functions we will enter the SQL queries directly into the SQL View of the query designer. To get to SQL View open an Access database then:

• Click the Queries tab in the left menu
• Click the 'Design' icon
• Add the table(s) you want to query to the query design view and close the Add table dialog box
• Click the small arrow next to the 'View' icon in the toolbar, select 'SQL View; from the drop down menu.

Please see string functions in SQL if you want to use these types of function with SQL Server as their name and syntax can vary slightly when using SQL Server.

The Trim function

The Trim(string) function removes any spaces from the beginning and end of the string that is passed to it. This is useful for getting rid of spaces that were accidentally added to the beginning or end of a text box entry when the data was input. The Trim function takes one parameter and that is the string to be trimmed. This can be a column name or an actual string.

UPDATE Customers SET FirstName = Trim(FirstName) 

The above statement trims the value in the FirstName column for every record in the Customers table.

SELECT TRIM('  some text    ') As 'MyString'

The above statement returns the column 'MyString' that contains the value 'some text'.

The LEFT and RIGHT functions

The LEFT(string, numberOfCharacters) and RIGHT(string, numberOfCharacters) functions provide a quick and convenient way to get the start or end portion of a string respectively. These functions take two parameters, the first is the string that you want to get the start or end of and the second is the number of characters from the start or end of the string that you want to retrieve.

SELECT LEFT(FirstName, 1) As Initial
FROM Customers

The above statement returns the first letter from the FirstName column of the Customers table and assigns the returned column an alias of 'Initial'.

The next example assumes we have a table called CompanyFiles that stores information about company documents. The table contains a column called 'FileSize' and a column called 'FilePath' that contains the path of a file such as 'C:\My Documents\Accounts\2007Invoices.xls'. We want to list the size of the file along with the file extension (i.e. .jpg, .doc, .xls etc). We can do this by using the RIGHT function as follows:

SELECT FileSize, RIGHT(FilePath, 4) As FileExtension
FROM CompanyFiles

The Len() Function

The Len(string) function returns the number of characters in a string. It is often used with other string functions to manipulate string data.

SELECT LEN(FirstName) As FirstNameLength
FROM Customers

The Instr() Function

The Instr function is often used in combination with other string functions for manipulating string values. The Instr function returns the position of a string occurring within another string. The format of the function is as follows:

Instr ( [start], stringToSearch, stringToFind)

The start parameter is optional and specifies where in the string we will start searching for the second string. If we wanted to start at the fifth character of the first string then we would specify 5 for the start parameter. If this is left blank then the search is started at the beginning of the first string.

An example of using the Instr function to find the position of a space in a full name would be as follows:

SELECT Instr('Joe Bloggs', ' ') As PosOfSpace

The above example would return 4 as the first match on the specified string 'Joe Bloggs' is at the 4th character. If the first string doesn't contain the second string then Instr returns 0. If the first string is NULL then Instr returns NULL.

The Mid Function

The Mid(string, start, length) function returns a portion of a string starting from a specified position and containing a specified number of characters.

The following extracts the month value from a string column in the format dd/mm/yy. The extracted string starts at the 4th character in the string and ends after 2 characters.

SELECT Mid(StartDate, 4, 2) As MonthValue

Combining the functions to give more powerful string manipulation

We have seen how to use a number of string functions and can now give an example of how to use them in combination to perform some more powerful string manipulation.

Suppose we have a table that has customer names stored in it but the full name is stored in one column rather than the first name in one column and last name in the other column. Without string manipulation we would not be able to extract just the last name from the name column. The functions we have learned in this article enable us to extract the LastName from the FullName column.

SELECT Mid(FullName, Instr(FullName,    ' ') + 1,
        Len(FullName) - Instr(FullName, ' ')) As LastName
FROM Customers

String functions in Design View

Although this article has focused on writing your queries in SQL view you can also use these functions in Design View. To use a function in design view simply open the query in design view and then in a new column enter the following into the Field row:

• The name (alias) that you want the column to be known as
• A colon - :
• The function name including any arguments in brackets. Note enclose column names in square brackets.

An example of this would be Initial:Left([FirstName], 1)

Conclusion

In this article we have seen how to use some of the most commonly used string functions in Microsoft Access. We have then demonstrated how these functions can be used to achieve tasks such as selecting specific parts of column values and giving uniform data formats. We have also shown how these functions can be used from within Query SQL View and also from within Query Design View.