Append a substring to the end of a string

Forum|Forum|18 years ago
April 16, 2007
5 replies
1238 views

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>

This topic has been closed for replies.

W

WebPexDev

Inspiring

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.

D

Dan_Bracuk

Inspiring

<cfquery>
select etc
#PreserveSingleQuotes(your_variable_goes_here)#

D

Dan_Bracuk

Inspiring

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

A

Anonymous

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

BKBK

Community Expert

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>

C

cf_dev2

Inspiring

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded