Skip to main content
Daniel_Eng
Participating Frequently
June 6, 2006
Answered

Problem with ListChangeDelims()

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>
This topic is closed to new replies. Start a new post to keep the conversation going.
BKBK
BKBKCorrect answer
Community Expert
June 6, 2006
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>



View reply

7 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
June 7, 2006
!
Translate
BKBK
Community Expert
BKBKCommunity ExpertCorrect answer
Community Expert
June 6, 2006
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>



Translate
Daniel_EngAuthor
Participating Frequently
June 7, 2006
Thanks... that could work too...
Translate
BKBK
Community Expert
BKBKCommunity Expert
Community Expert
June 6, 2006
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)




Translate
Daniel_EngAuthor
Participating Frequently
June 6, 2006
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?
Translate
Anonymous
June 6, 2006
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.
Translate
Daniel_EngAuthor
Participating Frequently
June 6, 2006
Can you give me a sample code? I'm using ODBC (Ms Access).
Translate
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices