How can I escape commas inside listQualify function?

[rp966435]

I

am a newbie at coldfusion. I need some help. My database has records that are company names as such: abc, Inc. For now I am using this query:

SELECT DISTINCT COMPANY FROM ComapanyTable WHERE (Company IN (#ListQualify(form.cCompanyList2, "'", ",")#))

This problem with this is that it separates the abc, Inc.

SELECT DISTINCT Company FROM CompanyTableWHERE (Company IN ('abc',' Inc.','xyz','Inc.'))

I need to get the list as it is, i.e. as: 'abc, Inc.', 'xyz, Inc' So that I can later insert these values into a new table.

Insert code:

<cfquery name="insertPair" datasource="#DSN#" dbtype="ODBC"> INSERT INTO tblChildCompanyToParent (Parent_Account_ID, Child_Account_ID) SELECT DISTINCT <cfqueryparam value = "#form.pCompanyList#" CFSQLType = "CF_SQL_VARCHAR">, Company FROM CompanyTable WHERE Company IN (<cfqueryparam value="#ValueList(insertSelect.Company,';' )#" CFSQLType = "CF_SQL_VARCHAR" list="true" separator=";">) </cfquery>

Select list code

<select multiple name="cCompanyList2" id="cCompanyList2" class="selectCCompany" data-attribute="selCCompany" size="10"> <cfloop query="childCompanyList"> <option value="#childCompanyList.Child_Account_ID#">#childCompanyList.Company#</option> </cfloop> </select>

Is there anyway to fix this.

[haxtbh]

You asked this questions on SO. What was wrong with answer that was provided? What have you tried that didn't work?

sql - How can I escape commas inside listQualify function? - Stack Overflow

[WolfShade]

Since the value of the multi-select results in a comma-delimited list (if more than one is chosen), then after the form is submitted and before the data is processed, do a REreplace() on that value.  Using RegEx, you can change the commas that DON'T have a space after them into another character (like a pipe | ) and use the other character as the list delimiter inside the CFQUERYPARAM tag.

So, if the value from the form is:

abc, inc.,xyz, inc.,123, inc.

Then the regex would be looking for a comma that doesn't have a space after it.

REreplace(form.selectName,",[^ ]+","|","all")

And since the values are coming from a SELECT (and if the company names are properly formatted; ie, no "abc,inc." - has to be "abc, inc."), you don't have to worry about the user entering the value, thusly potentially screwing things up. 

Not tested.

HTH,

^_^

[rp966435]

Hi

Thank you for helping.

I tried out what you said but this was the output i got:

abc, Inc.| Inc.

It ate up the xyz from the xyz, Inc.

I am unable to understand why?

[WolfShade]

Hmm.. maybe not a comma followed by "not a space".  A comma followed by a letter or number?

REreplace(form.selectName,",[\d|\w]+","|","all")  

See if that works.

HTH,

^_^

[rp966435 · Accepted solution]

Thank you for your help.

I managed to fix it.

I have posted the solution to StackOverflow

sql - How can I escape commas inside listQualify function? - Stack Overflow

Thank you again!