Skip to main content
K
kt03
Inspiring
June 1, 2011
Question

lcase, ucase and ??

  • June 1, 2011
  • 3 replies
  • 875 views

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

This topic has been closed for replies.

3 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
June 3, 2011

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
Owainnorth
Inspiring
June 3, 2011

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.

D
Dan_Bracuk
Inspiring
June 1, 2011

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.

A
Adam Cameron.
Inspiring
June 1, 2011

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

--

Adam

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices