Highlighted

How can I escape commas inside listQualify function?

Community Beginner ,
Mar 12, 2015

Copy link to clipboard

Copied

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.

Correct answer by rp966435 | Community Beginner

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!

Views

904

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

How can I escape commas inside listQualify function?

Community Beginner ,
Mar 12, 2015

Copy link to clipboard

Copied

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.

Correct answer by rp966435 | Community Beginner

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!

Views

905

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Mar 12, 2015 0
Advocate ,
Mar 12, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 12, 2015 0
LEGEND ,
Mar 12, 2015

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 12, 2015 0
Community Beginner ,
Mar 12, 2015

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 12, 2015 0
LEGEND ,
Mar 13, 2015

Copy link to clipboard

Copied

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,


^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 13, 2015 0
Community Beginner ,
Mar 16, 2015

Copy link to clipboard

Copied

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!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 16, 2015 0