Remove item from ValueList?

[braseth]

From a query I am creating a list using quotedValueList. There will be a need for me to remove an item or two from the list. When I currently do it, I'm left with single quotes and the comma. I want to have a clean list b/c i will eventually be passing the list into an SQL statement.

This is what I have.

<CFSET test=#QuotedValueList(getPerson.id,",")#>

Test : '1231','1232','1233'

#Replace(variables.test, '1232', "", "all")#

Test : '1231','','1233'

[braseth · Accepted solution]

<CFSET test=#QuotedValueList(getPerson.id,",")#>
first string: #test#<BR>
<CFSET deletePosition=#listfind(variables.test,"'1232'")#>
delete: #deletePosition#
<CFSET test=#listDeleteAt(variables.test,variables.deletePosition)#>
#variables.test#

[Newsgroup_User]

Then you need to also replace the quotes, not just the characters
between them

#Replace(variables.test,"'12321'","","all")#

[Dan_Bracuk]

Ways to improve on the existing answer.

1. If possible, strengthen the where clause of your first query so the value list has only the items you want.
2. In the 2nd query, use cfqueryparam list="yes". Then you can use a normal value list and not have to worry about quotes.

[Guest]

Folks, too many people make use #xxxxx# when it's totally unnecessary, it's inefficient and it's harder to read.

THIS

<CFSET test=QuotedValueList(getPerson.id,",")>
<CFSET deletePosition=listfind(variables.test,"'1232'")>
<CFSET test=listDeleteAt(variables.test,variables.deletePosition)>

NOT

<CFSET test=#QuotedValueList(getPerson.id,",")#>
<CFSET deletePosition=#listfind(variables.test,"'1232'")#>
<CFSET test=#listDeleteAt(variables.test,variables.deletePosition)#>

General rule, if you're not displaying it you probably don't need ##. If you aren't displaying it but it's inside quotes then you probably need it
e.g. <CFSET firstlast = "#RTrim(firstname)# #RTrim(lastname)#">
although an alternative is
<CFSET firstlast = Trim(firstname) & " " & Trim(lastname)>