| When using forms to power database searches, it is an all-too-common occurrence that the form user accidentally slips in an extra space when typing information into a search form. For example, if a user is searching by first name and types in Barry, the action page will query the database for everyone with the first name of Barry. Super. If the user's finger slips while entering the information into the form and he types Barry (note the blank space at the beginning of the string), our action page will query the database for a string that matches what was entered, blank space and all. Consequently, the user will probably not get any responses back (unless they were entered into the database with a leading space).To combat this problem we can use the ColdFusion Trim() function. This function removes any whitespace from the beginning and end of a nominated string. The Trim() function takes only one parameter, the name of the string you want to trim. For example, we can use the Trim() function in our SQL statements to make sure we get rid of any extra leading or trailing spaces from the form fields that get submitted. The following code illustrates the use of the Trim() function: <CFQUERY NAME="qMyQuery" DATASOURCE="MyDSN"> SELECT FirstName, LastName FROM Employees WHERE FirstName = '#Trim(FORM.FirstName)#' </CFQUERY> In the preceding example, if a user were to submit the string Barry, the Trim() function in our SQL statement would turn the string into Barry (minus the leading space) before we use it to query the database. Two other associated functions also perform a similar task. The LTrim() function removes extra whitespace from the left side of a string only. The RTrim() function removes extra whitespace from the right side of a string only. |