pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: tjk@tksoft.com <tjk@tksoft.com>
To: tgl@sss.pgh.pa.us
Cc: tjk@tksoft.com
Cc: danlyke@flutterby.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Case Insensitive Queries
Date: Wed, 30 May 2001 07:37:45 -0700 (PDT)
Message-ID: <200105301437.HAA30076@smtp3.tksoft.com> (raw)
In-Reply-To: <21545.991232634@sss.pgh.pa.us>


I use a few of them, and in my opinion there is a distinct group of
characters at last in the 8859-1 character set which have a lower and
upper case instance. The ranges are 0xC0 to 0xDD for upper case and 0xE0
to 0xFD for upper (with the exception of 0xD0, 0xD7, 0xF0, and 0xF7).
I haven't examined all the relevant docs, so I might be wrong.  The lists
are based on my own observations of the characters in question.

There is probably no harm in sending a few extra bytes, so I am appending
a related function below. If someone finds a flaw with the function,
please tell me; that would be greatly appreciated.

I am also including a list of related characters.  This email is going
out with 8859-1 as the charset, so I hope you are able to view them.




UPPER CASE:
192: À (0xc0)  193: Á (0xc1)  194: Â (0xc2)  195: Ã (0xc3)
196: Ä (0xc4)  197: Å (0xc5)  198: Æ (0xc6)  199: Ç (0xc7)
200: È (0xc8)  201: É (0xc9)  202: Ê (0xca)  203: Ë (0xcb)
204: Ì (0xcc)  205: Í (0xcd)  206: Î (0xce)  207: Ï (0xcf)
               209: Ñ (0xd1)  210: Ò (0xd2)  211: Ó (0xd3)
212: Ô (0xd4)  213: Õ (0xd5)  214: Ö (0xd6)
216: Ø (0xd8)  217: Ù (0xd9)  218: Ú (0xda)  219: Û (0xdb)
220: Ü (0xdc)  221: Ý (0xdd)  

LOWER CASE:
224: à (0xe0)  225: á (0xe1)  226: â (0xe2)  227: ã (0xe3)
228: ä (0xe4)  229: å (0xe5)  230: æ (0xe6)  231: ç (0xe7)
232: è (0xe8)  233: é (0xe9)  234: ê (0xea)  235: ë (0xeb)
236: ì (0xec)  237: í (0xed)  238: î (0xee)  239: ï (0xef)
               241: ñ (0xf1)  242: ò (0xf2)  243: ó (0xf3)
244: ô (0xf4)  245: õ (0xf5)  246: ö (0xf6)
248: ø (0xf8)  249: ù (0xf9)  250: ú (0xfa)  251: û (0xfb)
252: ü (0xfc)  253: ý (0xfd)

SKIPPED
208: Ð (0xd0) 
215: × (0xd7)
222: Þ (0xde)
240: ð (0xf0)  
247: ÷ (0xf7)
254: þ (0xfe)



CREATE FUNCTION lower8859_1 (text) RETURNS text
   AS '/usr/include/pgsql/lib/str8859_1.so'
   LANGUAGE 'C';




/* No warranty of any kind, use at your own risk. Use freely. 
 */

text * lower8859_1 (text * str1) {
   text * result;
   int32 len1  = 0, i;
   unsigned char * p, * p2, c;
   unsigned char upper_min = 0xC0;
   unsigned char upper_max = 0xDD;

   len1 = VARSIZE(str1) - VARHDRSZ;

   if (len1 <= 0)
      return str1;

   result = (text *) palloc (len1 + 2 + VARHDRSZ);
   if (! result)
      return str1;

   memset (result, 0, len1 + 2 + VARHDRSZ);

   p = VARDATA(result);
   p2 = VARDATA(str1);

   for (i=0; i < len1; i++) {
      c = p2[i];
      if (isupper(c) || (c >= upper_min && c <= upper_max && c != 0xD0 && c != 0xD7))
         p[i] = c + 0x20;
      else
         p[i] = c;
   }

   VARSIZE(result) = len1 + VARHDRSZ;

   return result;
}




Troy








 
> "tjk@tksoft.com" <tjk@tksoft.com> writes:
> > If upper() and lower() operate on characters in 8859-1 and other character
> > sets when the appropriate locale is set, then a difference in the behavior
> > of upper() and lower() would seem like a bug.
> 
> Au contraire ... upper() and lower() are not symmetric operations in
> quite a few non-English locales.  I'll let those who regularly work with
> them give specific details, but handling of accents, German esstet (sp?),
> etc are the gotchas that I recall.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 




view thread (19+ messages)  latest in thread

Message-ID: <200105301437.HAA30076@smtp3.tksoft.com>
Permalink:  ../200105301437.HAA30076@smtp3.tksoft.com/
Also on:    postgresql.org/message-id/200105301437.HAA30076@smtp3.tksoft.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tjk@tksoft.com, tgl@sss.pgh.pa.us, danlyke@flutterby.com
  Subject: Re: Case Insensitive Queries
  In-Reply-To: <200105301437.HAA30076@smtp3.tksoft.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox