lcase, ucase and ??

[kt03]

Hi,

i can check for lcase (test), ucase (TEST) but what's about Test?

  SELECT org FROM center

  WHERE center= <cfqueryparam value="#trim(lcase(form.center))#" cfsqltype="cf_sql_varchar" maxlength="50" > ---test
  or
  center= <cfqueryparam value="#trim(ucase(form.center))#" cfsqltype="cf_sql_varchar" maxlength="50" > ---TEST

or center= ????--Test?

Thanks

[Adam Cameron.]

There's no inbuilt function to do that.  However cflib.org almost certainly has a UDF that does...

--

Adam

[Dan_Bracuk]

Database functions upper() and lower() will work.  Having said that, using db functions in the where clause can really slow down production.  If there are some indexed fields you can use first, do so.

[BKBK]

kt03 wrote:

Hi,

i can check for lcase (test), ucase (TEST) but what's about Test?

  SELECT org FROM center

  WHERE center= <cfqueryparam value="#trim(lcase(form.center))#" cfsqltype="cf_sql_varchar" maxlength="50" > ---test
  or
  center= <cfqueryparam value="#trim(ucase(form.center))#" cfsqltype="cf_sql_varchar" maxlength="50" > ---TEST

or center= ????--Test?

Place this line before the cfquery tag:

<cfset centerfield = trim(form.center)>

Then apply:

WHERE center = <cfqueryparam value="#ucase(left(centerfield, 1)) & right(centerfield,len(centerfield) - 1)#" cfsqltype="cf_sql_varchar" maxlength="50" >

[Owainnorth]

Does your database not support function-based indexing? This is what I'd do in Oracle:

CREATE INDEX center_whatever_idx

ON center ( UPPER(org) ) ;

That's then created an index on the upper-case version of your column. You can then just search for it in uppercase:

<cfquery>

  SELECT something

  FROM center

  WHERE UPPER(center) = <cfqueryparam cfsqltype="cf_sql_varchar" value="#upper(myval)#" />

</cfquery>

And that'll use your index. I haven't tested it, but I'd be willing to bet it's a million times quicker.

[Dan_Bracuk]

How would that approach work with these values?

John Paul Jones

McBean