Upper & Lower Case 1

[varnix]

I am reading data from a CSV text file and when doing the query against a form field I get no results returned... unless I exactly match the case of the word in the file.

This is a file of all employees and their departments, I use the query:

<cfquery name=&quot;first&quot; datasource=&quot;empinfo&quot;>
select *
from bassett.csv
where fname like '%#form.firstname#%'
</cfquery>

If I do a search on a portion of my own name - &quot;Rog&quot; it will find all instances of &quot;Roger&quot; in the file. But if I search on &quot;rog&quot; it finds nothing.

Is there a command or a setting that will allow me to not be case specific?

Thanks!

Roger

 

[tleish]

I don't know what db drivers you are using to search through the csv file, but if it has a lcase (lower case) function then you could do something like this, using LCase() CF Function and an LCase() sql function.

=== START CODE EXAMPLE ===
<cfquery name=&quot;first&quot; datasource=&quot;empinfo&quot;>
select *
from bassett.csv
where LCase(fname) like '%#LCase(form.firstname)#%'
</cfquery>
=== END CODE EXAMPLE ===


or maybe:

=== START CODE EXAMPLE ===
<cfquery name=&quot;first&quot; datasource=&quot;empinfo&quot;>
select *, LCase(fname) as search_fname
from bassett.csv
where search_fname like '%#LCase(form.firstname)#%'
</cfquery>
=== END CODE EXAMPLE === - tleish

 

[varnix]

Thanks for the assist!

During one of my previous tests I had change to the Merant text driver. Lcase does not function with Merant, but it does work with the MS Text Driver.