Re: Case Insensitive Queries

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
>