On 2010-05-04 08:53:01 -0700, Larry W. Virden wrote: > On May 4, 11:22 am, martin.ev...@easysoft.com (Martin Evans) wrote: > > I'd really avoid concatenating user supplied strings into your SQL. Use > > parameters instead: > > > > select * from my_table where last_name like ? > > > > then use bind_param or exeute(my_parameter_list). > > That sounds reasonable. I inherited this code, written about 10 years > ago - it doesn't surprise me that a freshening is needed. > > > > You don't say what database this is. It is not always UPPER especially > > when the strings might be unicode (Oracle uses nls_upper). Other than > > that upper(column) = upper(?) is ok. > > This is ORACLE, and so perhaps it would be better to use nls_upper > instead of upper.
It depends. nls_upper has an extra parameter which lets you specify how
the conversion should be done. This is for the cases where a simple 1:1
mapping is not good enough. I currently know of only two of them:
* In German, lower case ß (one letter) maps to upper case SS (two
letters), but the conversion is not reversible.
* In most languages, lower case i (with dot) maps to upper case I
(without dot), but in Turkish languages, i (with dot) maps to İ (with
dot) and ı (without dot) maps to I (without dot).
If your application has to deal with these characters AND has a way to
distinguish which language-specific rules should be used, then use
nls_upper. Especially the second condition is important: If there is no
way (for the user or admin) to select the right rules, nls_upper gains
you nothing and you might as well use upper.
There is also the question of what you want to achieve by using upper:
It looks like you want to find a string regardless of capitalization.
But maybe you also want to ignore accents or other spelling variants
(for example, in German "ö" can also be written "oe"). Now it depends
not only on the language but on context and on the expectations of the
user. And once you do that you might want to tolerate spelling errors
...
hp
--
_ | Peter J. Holzer | Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR | eingeprägt stehen: "Ein ewig Rätsel will ich
| | | h...@wsr.ac.at | bleiben, mir und andern."
__/ | http://www.hjp.at/ | -- Wolfram Heinrich in desd
signature.asc
Description: Digital signature
