Inspiring

Question

lcase, ucase and ??

Forum|Forum|15 years ago
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

Getting started

This topic has been closed for replies.

BKBK

Community Expert

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:

Then apply:

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

Owainnorth

Inspiring

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:

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

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

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

--

Adam

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded