IDS Forum

Re: Next version of IDS (post 11.50) has WHAT?

Posted By: Andrew Clarke
Date: Tuesday, 13 October 2009, at 8:42 p.m.

In Response To: Re: Next version of IDS (post 11.50) has WHAT? (Fernando Nunes)

> Just for curiosity, and if you care to spare me the trouble of looking
> up... How does SQL Server do that? Your query per si, if it matches "test"
> against "TEST" would be a wrong result in any RDBMS.

I think SQL server gets it wrong by going too far. Depending on the character
set you use, the caseless applies either to all CHAR or to no CHAR field. You
don't get a choice as far as I know.

Depends on the logical domain of the column.

For a name-like or annotation type of field, it's pretty clear that in the
real world where any illiterate can get a job doing data entry, it's a Good
Thing to be able to find "John smith" when you search for "John Smith" - in
fact, it's probably a nice thing for even a good end user to be able to search
for "john smith" without regard for case; it's just easier. Try to imagine how
annoying Google would be if it took your case literally.

For a code-like column - eg regional states, Y/N, ("ORD", "INV", "PST", "CAN",
etc etc etc) it's probably not a good idea to have caseless matches; then
again it should be harmless if all the codes are stored properly in the
ALLUPPER form. Constraints and/or triggers can easily enforce that.

You can easily think of many column types in your own world where you care or
don't care about case.

Anyway, to answer your question, if you are using a caseless character set in
Sequel server, then ALL character tests are caseless. It just happens. We
actually demanded case sensitive installs out of fear of the unknown, but one
of the customers secretly installed a caseless engine and basically beta'd the
whole issue for us. Turned out to be no big deal, so now all our Sequel
installs are caseless.

> Unless of course you have some kind of column or index property to specify
> that it should be case insensitive...
> And if that is so, how does it handle the search? Does it write the index
> using UPPER or LOWER, and then converts the argument so that an index match
> can be found?
>

All the Sequel internals are probably using locale-based functions such as
stricmp or even more advanced functions that account for the worlds quota of
languages using locales, collation tables and code equivalence charts. You
should try reading about Unicode in depth, it's quite entertaining/torturous.
At least Unicode can subsume all the features of single-use encoding schemes
such as JIS, the Latin-* extensions to ASCII or the monster sets used in China
and Asia.

Messages In This Thread

• Next version of IDS (post 11.50) has WHAT?
  Andrew Clarke -- Tuesday, 13 October 2009, at 2:10 a.m.
  • Re: Next version of IDS (post 11.50) has WHAT?
    Art Kagel -- Tuesday, 13 October 2009, at 6:37 a.m.
    • Re: Next version of IDS (post 11.50) has WHAT?
      Andrew Clarke -- Tuesday, 13 October 2009, at 6:34 p.m.
      • Re: Next version of IDS (post 11.50) has WHAT?
        Obnoxio The Clown -- Tuesday, 13 October 2009, at 6:42 p.m.
        • Re: Next version of IDS (post 11.50) has WHAT?
          Andrew Clarke -- Tuesday, 13 October 2009, at 7:05 p.m.
          • Re: Next version of IDS (post 11.50) has WHAT?
            Obnoxio The Clown -- Tuesday, 13 October 2009, at 7:09 p.m.
            • Re: Next version of IDS (post 11.50) has WHAT?
              Andrew Clarke -- Tuesday, 13 October 2009, at 7:31 p.m.
              • Re: Next version of IDS (post 11.50) has WHAT?
                Obnoxio The Clown -- Tuesday, 13 October 2009, at 7:36 p.m.
                • Re: Next version of IDS (post 11.50) has WHAT?
                  Andrew Clarke -- Tuesday, 13 October 2009, at 7:49 p.m.
          • Re: Next version of IDS (post 11.50) has WHAT?
            Adam Tauno Williams -- Tuesday, 13 October 2009, at 8:09 p.m.
            • Re: Next version of IDS (post 11.50) has WHAT?
              Andrew Clarke -- Tuesday, 13 October 2009, at 8:30 p.m.
              • Re: Next version of IDS (post 11.50) has WHAT?
                Adam Tauno Williams -- Wednesday, 14 October 2009, at 6:24 a.m.
                • Re: Next version of IDS (post 11.50) has WHAT?
                  Adam Tauno Williams -- Wednesday, 14 October 2009, at 6:41 a.m.
          • Re: Next version of IDS (post 11.50) has WHAT?
            Fernando Nunes -- Tuesday, 13 October 2009, at 8:12 p.m.
            • Re: Next version of IDS (post 11.50) has WHAT?
              Andrew Clarke -- Tuesday, 13 October 2009, at 8:42 p.m.
          • Re: Next version of IDS (post 11.50) has WHAT?
            John Miller iii -- Tuesday, 13 October 2009, at 8:22 p.m.
            • Re: Next version of IDS (post 11.50) has WHAT?
              Fernando Nunes -- Tuesday, 13 October 2009, at 8:30 p.m.
              • Re: Next version of IDS (post 11.50) has WHAT?
                Andrew Clarke -- Tuesday, 13 October 2009, at 8:49 p.m.
            • Re: Next version of IDS (post 11.50) has WHAT?
              Andrew Clarke -- Tuesday, 13 October 2009, at 8:43 p.m.
  • Re: Next version of IDS (post 11.50) has WHAT?
    ÖÜÐÛ -- Tuesday, 13 October 2009, at 9:27 p.m.
    • Re: Next version of IDS (post 11.50) has WHAT?
      Andrew Clarke -- Tuesday, 13 October 2009, at 10:30 p.m.
      • Re: Next version of IDS (post 11.50) has WHAT?
        Andrew Clarke -- Tuesday, 13 October 2009, at 11:06 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.