Comparing two character values in cfquery Coldfusion
I have a database field that contains VARCHAR
values like
XY23(CX Web)
And I am writing request requests like
SELECT qPdfs.filename, qPdfs.code, qObjects.id
FROM qPdfs, qObjects
WHERE qPdfs.code = qObjects.code
OR, for example,
SELECT qPdfs.filename, qPdfs.code, qObjects.id
FROM qPdfs, qObjects
WHERE <cfqueryparam value="#qObjects.code#" cfsqltype="CF_SQL_VARCHAR"> = <cfqueryparam value="#qPdfs.code#" cfsqltype="CF_SQL_VARCHAR">
But I am getting an error like
XY23(CX Web) must be interpretable as a valid number in the current locale.
Any help?
thank
source to share
Are both database columns varchar
? If so, QoQ is probably applying some implicit conversion based on the content of the columns. If some of the codes are numeric, QoQ may try to cast them to numbers inside before doing the comparison. Hence the error. cfqueryparam
won't help here as it can only be used in literals and not in query columns. If the comparison is direct comparisons, i.e. ColName = ColName
, causes this error, try casting instead:
WHERE CAST(qPdfs.code AS VARCHAR) = CAST(qObjects.code AS VARCHAR)
Note. QoQ string comparison is case sensitive, so you can convert columns to upper / lower case first.
source to share