Problem with ListChangeDelims()

[Daniel_Eng]

I can't get CF to return the results that I want... is there anything wrong with this code?

<cfif isDefined('form.ICNumber')>
<cfquery name="findRegistrant" datasource="registrationdb" dbtype="odbc">
SELECT partner.*
FROM partner
WHERE 'ListChangeDelims(partner.ICNumber,"","-")' = '#ListChangeDelims(AppendICNumber,"","-")#'
</cfquery>
</cfif>

[MikerRoo]

There are many things wrong with that code.

The biggest problem is trying to use a CF function, ListChangeDelims, as a SQL operator.

You need to use string functions provided by by whatever RDBMS you are using.

[Daniel_Eng]

Can you give me a sample code? I'm using ODBC (Ms Access).

[BKBK]

There should be a reason, after all, for testing whether the form field exists.

<cfif isDefined("form.ICNumber")>
<cfquery name="findRegistrant" datasource="registrationdb">
SELECT *
FROM partner
where ICNumber = '#form.ICNumber#'
</cfquery>
</cfif>
(assumes ICNumber and form.ICNumber are strings)

[Daniel_Eng]

WHERE 'ListChangeDelims(partner.ICNumber,"","-")' = '#ListChangeDelims(AppendICNumber,"","-")#'

The reason why i did this is because the ICNumber stored in my DB can be either be something like '123456-45-9876' or 123456459876. A user can either key in something like'123456-45-9876' or 123456459876. They are both fundametally the same data keyed in differently with the hyphen. So how can I get ColdFusion to still return the results whether the user runs a search with or without those hyphen?

[BKBK · Accepted solution]

Assuming '123456-45-9876' and '123456459876' are the only two possible representations,

<cfset ic_no = Replace(AppendICNumber,"-","","all")>
cfif isDefined("form.ICNumber")>
<cfquery name="findRegistrant" datasource="registrationdb">
SELECT *
FROM partner
where ICNumber = '#AppendICNumber#' OR ICNumber = '#ic_no#'
</cfquery>
</cfif>

[Daniel_Eng]

Thanks... that could work too...

[BKBK]

!