Postgres Case sensitivity

[Andy Hilton]

Will

Check the ‘UPPER’ function in postgres - that may give you waht you need - in either way you will need specific sql whether using ilike or the upper function……

Andy

Andy Hilton
Totally Brilliant Software Inc
Phone (US) : (863) 858 4000 
Phone (UK) : 0207 193 8582
Web : www.totallybrilliant.com <http://www.totallybrilliant.com/> 
Helpdesk : http://totallybrilliant.kayako.com
Email : andyh at totallybrilliant.com

> On Jun 14, 2019, at 7:10 AM, Will Adkin via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:
> 
> 
> Hi List
> 
> At Lineal we've just spent the past year and a half converting customer from Frontbase to Postgres and we've just discovered a problem - Postgres does not seem to support case insensitive collations!
> 
> With FB we had the ability to specify a case insensitive coalition on every string column, which meant that searches on codes did not have to be case sensitive. WHERE company_name = 'lineal' would return a record with company_name = 'Lineal' and vice versa
> 
> No so now...
> 
> I'm aware the ILIKE function returns a case insensitive match, but before we embark on a massive project to replace most of our SQL Where clauses (Swapping '=' with iLIKE) is there another way that would be better?
> 
> Another option would be to replace all VARCHAR columns with CITEXT across the board...not a simple undertaking and CITEXT does not seem to be a core datatype, I'm not sure how reliable it would be?
> 
> Right now we're implementing ILIKE in places that need it most... anyone else come across this problem and came up with a neat way to get around it?
> 
> 
> 
> Best regards
> 
> Will
> 
> 
>          <http://www.lineal.co.uk/>        
> 
> <https://twitter.com/linealsoftware>   <https://www.facebook.com/linealsoftware?fref=ts&ref=br_tf>   <https://plus.google.com/112982815000763473226/about>   <https://www.linkedin.com/company/1859834?trk=tyah&trkInfo=clickedVertical%3Acompany%2Cidx%3A1-1-1%2CtarId%3A1437729256906%2Ctas%3Alineal%20s>		
> Will Adkin  / Software Development Manager  
> will at lineal.co.uk <mailto:will at lineal.co.uk>
> Lineal Software Solutions  
> 01271 375999  
> Commercial House, The Strand
> Barnstaple, Devon, EX31 1EU  
> www.lineal.co.uk <http://www.lineal.co.uk/>
> 
> 
> This e-mail message may contain confidential or legally privileged information and is intended only for the use of the intended recipient(s). Any unauthorized disclosure, dissemination, distribution, copying or the taking of any action in reliance on the information herein is prohibited. E-mails are not secure and cannot be guaranteed to be error free as they can be intercepted, amended, or contain viruses. Anyone who communicates with us by e-mail is deemed to have accepted these risks. Lineal is not responsible for errors or omissions in this message and denies any responsibility for any damage arising from the use of e-mail. Any opinion and other statement contained in this message and any attachment are solely those of the author and do not necessarily represent those of the company.
> 
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com