CFLib.org – Common Function Library Project

trimQuery(qry)

Last updated February 26, 2004

author

Giampaolo Bellavite

Version: 1 | Requires: CF5 | Library: DataManipulationLib

Description:
Trims spaces from all recordset in a query. Useful for databases that retrieve varchars values with extra spaces (as SQL Server). Note - it is preferable to have the database do your trim for you. SQL Server for example can easily trim the data returned.

Return Values:
Returns a query.

Example:

<cfset qryFoo = queryNew('FirstName,LastName')>
<cfset queryAddRow(qryFoo, 3)>
<cfset querySetCell(qryFoo, 'FirstName', '  Ben   ', 1)>
<cfset querySetCell(qryFoo, 'LastName', '  Forta        ', 1)>
<cfset querySetCell(qryFoo, 'FirstName', 'Massimo   ', 2)>
<cfset querySetCell(qryFoo, 'LastName', 'Foti       ', 2)>
<cfset querySetCell(qryFoo, 'FirstName', '    Ju', 3)>
<cfset querySetCell(qryFoo, 'LastName', 'Ratto       ', 3)>
<pre>
<strong>Not Trimmed</strong>:
<cfoutput query="qryFoo">#qryFoo.FirstName# #qryFoo.LastName#<br></cfoutput>
<cfset qryFoo=TrimQuery(qryFoo)>
<strong>Trimmed</strong>:
<cfoutput query="qryFoo">#qryFoo.FirstName# #qryFoo.LastName#<br></cfoutput>
</pre>

Parameters:

Name Description Required
qry The query to trim. Yes

Full UDF Source:

/**
 * Trim spaces from all records in a query.
 * 
 * @param qry 	 The query to trim. (Required)
 * @return Returns a query. 
 * @author Giampaolo Bellavite (giampaolo@bellavite.com) 
 * @version 1, February 26, 2004 
 */
function trimQuery(qry) {
	var col="";
	var i=1;
	var j=1;
	for(;i lte qry.recordCount;i=i+1) {
		for(j=1;j lte listLen(qry.columnList);j=j+1) {
			col=listGetAt(qry.columnList,j);
			querySetCell(qry,col,trim(qry[col][i]),i);
		}
	}
	return qry;
}
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson