SQL Select Statement for Upper Lower Case Count

[Doug Easterbrook]

and you didn’t ask, but I would also load the extension for unaccent and use that as well.   helps deal with most latin characters where the person may or may not have put them in the database



eg. in omnis

calculate name as ‘Mañuel’

suppose the name in the database is ‘manuel’ (there is no accent on the ’n’)




sql where clause, to get it right would be

sta: where
sta: lower(unaccent(first_name)) = lower(unaccent(‘[pName]’))









Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978

> On Nov 19, 2024, at 2:55 PM, philip.tulett <philip.tulett at pdq-networks.com> wrote:
> 
> Hi Andy,Martin says Postgresql in the first line of his email.Kind regards Phil 
> -------- Original message --------From: Andy Hilton <andyh at totallybrilliant.com> Date: 19/11/2024  22:35  (GMT+00:00) To: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com> Subject: Re: SQL Select Statement for Upper Lower Case Count MartinIt depends on the database in question…..which one are you using ?Andy HiltonTotally Brilliant Software IncPhone (US) : (863) 409 4870 Phone (UK) : 0207 193 8582Web : www.totallybrilliant.com Helpdesk : https://totallybrilliantsoftware.supportsystem.com/Email : andyh at totallybrilliant.com> On Nov 19, 2024, at 5:29 PM, Martin Obongita via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:> > Hello everyone,> I have this statement in postgresql that fetched all records that match the supplied value and returns a count: /* > Select (count(1)) from person> > Where first_name=@[pFirstName]> > */> The challenge is that if the first_name has both upper and lower case entries, then it does not return any value if I supply it with a lowercase value.> Is there a sql select statement that could run and would ignore the case of the of the column and return a value for both uppercase and lowercase columns?> Martin.> _____________________________________________________________> Manage your list subscriptions at https://lists.omnis-dev.com> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com _____________________________________________________________Manage your list subscriptions at https://lists.omnis-dev.comStart a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com