Problem Using SQL Like Condition On Column With Null Values

Thanks Azadi.

just couple more suggestions:
1) VAR your getClubs qoq. add this after the last <cfargument>:
<cfset var getClubs = "">

2) trim() your varchar arguments

3) use <cfqueryparam>!!!

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Ok. For reference here is my final function. I've made a few further amendments and removed the "group by" which I don't really need.

***

<!--- Extract clubs from database --->
<cffunction name="getClubs" access="public" returntype="query" hint="Extract clubs from database">
<cfargument name="regionID" type="numeric" required="false" default="0" />
<cfargument name="activityID" type="numeric" required="false" default="0" />
<cfargument name="town" type="string" required="false" default="" />
<cfargument name="postcode" type="string" required="false" default="" />
<cfargument name="freeTrialOnly" type="numeric" required="false" default="0" />
<cfargument name="keyword" type="string" required="false" default="" />

<cfif not StructKeyExists(application,"getClubs")>
<cflock type="exclusive" scope="application" timeout="30">
<cfquery name="application.getClubs" datasource="#variables.ds#" username="#variables.dsUsername#" password="#variables.dsPassword#">
select clubs.clubID, packages.package, clubs.packageID, classifications.classification, clubs.classificationID, clubs.companyRegNo, regions.region, clubs.regionID, clubs.latitude, clubs.longitude, clubs.telephone, clubs.email, clubs.website, clubs.firstName, clubs.lastName, clubs.numberOfMembers, clubs.freeTrial, activityID, coalesce(clubs.facilityName,"") as facilityName, coalesce(clubs.tradingName,"") as tradingName, coalesce(clubs.address1,"") as address1, coalesce(clubs.address2,"") as address2, coalesce(clubs.address3,"") as address3, coalesce(clubs.town,"") as town, coalesce(clubs.county,"") as county, coalesce(clubs.postcode,"") as postcode
from (((classifications right join clubs on classifications.classificationID = clubs.classificationID) left join packages on clubs.packageID = packages.packageID) left join regions on clubs.regionID = regions.regionID) left join clubactivitylinks on clubs.clubID = clubactivitylinks.clubID
</cfquery>
</cflock>
</cfif>

<cfquery name="getClubs" dbtype="query">
select *
from application.getClubs
where clubID <> 0
<cfif arguments.regionID neq 0>
and regionID=<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.regionID#" />
</cfif>
<cfif arguments.activityID neq 0>
and activityID=<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.activityID#" />
</cfif>
<cfif Len(arguments.town)>
and town=<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.town#" />
</cfif>
<cfif Len(arguments.postcode)>
and lower(postcode) like '%#lcase(left(arguments.postcode,2))#%'
</cfif>
<cfif arguments.freeTrialOnly neq 0>
and freeTrial <> ''
</cfif>
<cfif Len(Trim(arguments.keyword))>
and
(
lower(facilityName) like '%#lcase(arguments.keyword)#%'
or lower(town) like '%#lcase(arguments.keyword)#%'
or lower(postcode) like '%#lcase(arguments.keyword)#%'
or lower(tradingName) like '%#lcase(arguments.keyword)#%'
or lower(address1) like '%#lcase(arguments.keyword)#%'
or lower(address2) like '%#lcase(arguments.keyword)#%'
or lower(address3) like '%#lcase(arguments.keyword)#%'
or lower(county) like '%#lcase(arguments.keyword)#%'
)
</cfif>
order by facilityName
</cfquery>

<cfreturn getClubs />
</cffunction>

***

Wouldn't "select distinct" accomplish the same thing?

Azadi - It's really lazy I know, but the data that I am having to work with is very poor quality and includes some duplicate records. I used group by to get rid of the duplicates.

Kronin555 - Yes. That's fixed it.

Thank you very much everyone for your help.

If you wrap your potentially-null columns with:
coalesce(possibleNullColumn,'')
all null values will be replaced with empty strings. Do this in the getClubs query, then see if you still run into problems with your QofQ query.

just curious - why do you need a GROUP BY clause in your query when you
are not using any aggregate functions?

in any case: in MySQL you do NOT need to specify ALL non-aggregate
columns used in your SELECT clause in the GROUP BY clause; specifying
just the ONE you want grouped by is enough. remove all but ClubID from
your GROUP BY clause and see if it works. but, really, i see no reason
for that group by clause at all...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

I forgot to mention that I am using a MySQL database.

Adam - I think you may be right. I seem to remember having a similar problem ages ago which (I think) I fixed by adding some additional code to my SQL statement.

I *think* you have interpretted the issue correctly, and the other people
responding are *possibly* giving you a bum steer.

QoQ definitely *does* have issues with null values, and I've encountered
exactly what you're seeing with ORDER BY clauses, in the past.

I've worked aroundthis by making sure I *didn't* have nulls in the data
being ordered. Using NVL() or some equivalent construct in your specific
DB.

--
Adam