Append a substring to the end of a string

[Guest]

I'm trying to dynamically generate my "where" clause for a SQL query. I'd like to append a substring to the end of an existing string. I tried "ListAppend", but of course that puts a comma between the original string and the substring. I could use "Insert" string function, but it seems rather a hassle to have to LEN the existing string each time I want to append something to the end of it. Is there an easy way to append a substring to the end of a string?

Here's an example of the code (using the "ListAppend" that doesn't work):

<CFSET whereStmt = "ads.ad_duedate + INTERVAL 1 DAY >= NOW()">

<cfswitch expression="#vendorSort#">
<cfcase value="Yes"><CFSET whereStmt = ListAppend(whereStmt, " AND ads.ad_vendor = #AdSortBy#")></cfcase>
</cfswitch>

[cf_dev2]

You could skip the length checks and always add an "AND" in front of each condition.

AND column1 = 2
AND column2 = 'ABC'
AND column3 = 156

After you've added all of your conditions, simply remove the first "AND" at the beginning of the string.

Another option is to build the sql statement inside your cfquery and skip the variable.

[BKBK]

Why don't you use an ampersand(&) to concatenate the strings?

<cfswitch expression="#vendorSort#">
<cfcase value="Yes">
<CFSET whereStmt = whereStmt & " AND ads.ad_vendor = #AdSortBy#")>
</cfcase>
<cfcase value="No">
<CFSET whereStmt = whereStmt & " AND ads.ad_vendor = #something_else#")>
</cfcase>
</cfswitch>

[Dan_Bracuk]

In addition to what BKBK said, when you put your variable into your cfquery, you will probably have to use the preservesinglequotes function.

[Guest]

Thanks, BKBK. The ampersand concatenation was the solution I was looking for.

Dan: Would the syntax for the "preservesinglequotes" function be:

<CFSET whereStmt = whereStmt & " AND ads.ad_vendor = #preserveSingleQuotes(something_else)#">

Thanks for your help!

max

[Dan_Bracuk]

<cfquery>
select etc
#PreserveSingleQuotes(your_variable_goes_here)#

[WebPexDev]

On additional suggestion...

You can use WHERE 0=0 as the first comparison in your where clause. This obviously is always true. The benefit is now you can have as many if statements adding conditions you want without have to worry about generating an invalid sql statement such as WHERE AND userid = 5 ie

<cfset var sql = "">
<cfset sql = "SELECT * FROM Users WHERE 0=0 ">
<cfif GetOnlyActiveUsers()>
<cfset sql = sql & "AND Active = 1 ">
</cfif>

Note that there is a space at the end of every line to ensure separation of statements.