Issues with Like operator in ColdFusion Query of Query and solution

This post is regarding one of my recent findings on LIKE operator in ColdFusion query of query(QoQ). Please find the details below. Hope this helps.

1.       What is Query of Query(QoQ) in ColdFusion?

a.       When you run a query on another query, we call it QoQ. This base query could be obtained from Database or may be from cfdirectory or any other source.

2.       Issue with Like Operator and QoQ

a.      In SQL server, Like operator makes Case Insensitive search. So writing column_name Like ‘%abc%’ or ‘%ABC%’ returns the same result.

b.      This same principle does not work when you write Like operator, in QoQ. QoQ makes case sensitive search. This will result in wrong result set.

3.       Addressing issue with Like operator in QoQ

a.       To address this issue, we need to compare both sides of Like operator in same case.

b.      Ex. WHERE UPPER(item.item_name) LIKE ‘%#UCase(itemName)#%’

c.       WHERE LOWER(item.item_name) LIKE ‘%#LCase(itemName)#%’