CODEWITHSUNDEEP
sqllistcoldfusion
BlackM
BlackM

Reputation: 4065

Coldfusion - Comma delimiter with comma inside value

I am building a list using SQL data and I am trying to make each value of the list as: 'value1',value2','value,4' and so on. My problem is that I am using this code:

(
    SELECT COUNT(ns.ticket)
    FROM ns_trade ns
    WHERE ns.[login]=mt.[login]
        AND 
        <cfif qGetCommentsAccounting.recordCount gt 0> 
            ns.COMMENT IN ('#listChangeDelims(qGetCommentsAccounting.list_comments, "','")#')
        <cfelse>
            1=2
        </cfif>
)as no_of_tickets_accounting

which is works perfect EXCEPT when my value has comma inside like 'value,4'. Any suggestions how to solve that?

Upvotes: 0

Views: 302

Answers (2)

BlackM
BlackM

Reputation: 4065

I fix the problem changing my query to:

<cfquery name="qGetComments" datasource="#application.dsn#">
            SELECT (STUFF((SELECT ',' +CHAR(39) +CAST(comment as varchar(50)) +CHAR(39)
                               FROM tbl_comment
                               WHERE report_type = <cfqueryparam value="#arguments.type#" cfsqltype="cf_sql_varchar">
                               AND report_id = <cfqueryparam value="#arguments.report_id#" cfsqltype="cf_sql_integer">
                               FOR XML PATH('')),1, 1, '')) as list_comments
        </cfquery>

where CHR(39) is the single quote and later on the other query (wich is inside an cfsavecontent):

(SELECT COUNT(ns.ticket)
                          FROM ns_trade ns
                          WHERE ns.[login]=mt.[login]
                          AND <cfif qGetCommentsAccounting.recordCount gt 0> ns.COMMENT IN (#qGetCommentsAccounting.list_comments#)
                              <cfelse>
                                1=2
                              </cfif>)as no_of_tickets_accounting

and execute it with PreserveSingleQuotes()

Upvotes: 0

Stefan Braun
Stefan Braun

Reputation: 400

If both queries work on the same database, it would be way more KISS to put them together. Usually, you should try to do as much as possible within your database.

SELECT 
    COUNT(ns.ticket)
FROM 
    ns_trade ns
WHERE 
    ns.[login] = mt.[login]
AND 
    ns.COMMENT IN 
        (
        SELECT 
            comment
        FROM 
            tbl_comment 
        WHERE 
            report_type = <cfqueryparam value="#arguments.type#" cfsqltype="cf_sql_varchar">
        AND 
            report_id = <cfqueryparam value="#arguments.report_id#" cfsqltype="cf_sql_integer">
        )

Upvotes: 2

Related Questions