How to compare two columns ignoring Case (Upper or Lower case)

Shibashis_Beher · August 2013

Vertica comparision is seems case specific Shiba is not equal to SHIBA. I want to ignore cases when compairing. Is there a shortcut to set session paramater for this instead doing UPPER(A) = UPPER(B)

[Deleted User] · August 2013

Hi Shibashis, Vertica supports Unicode (ie., non-English) strings; as a result, there are actually multiple ways to do a case-insensitive comparison. Take a look at database locales; specifically the 'collation' option: https://my.vertica.com/docs/6.1.x/HTML/index.htm#13786.htm https://my.vertica.com/docs/6.1.x/HTML/index.htm#14070.htm I suspect one (or more) of these will suit your needs. Adam

Daniel_Leybovic · August 2013

Hi! You can use in ILIKE: daniel=> select 'Shiba' ilike 'SHIBA'; ?column? ---------- t (1 row) daniel=> select 'Vertica' ~~* 'VerTicA'; ?column? ---------- t (1 row)

Shibashis_Beher · August 2013

I have many cases in where condition and i do not want to use UPPER or ilike clause in each and every comparison. i want to know if we can alter any session parameter that will by default compare all fields ignoring cases in that session?

[Deleted User] · August 2013

Hi Shibashis, I pointed you at the relevant parameters in my previous post; are they working well for you? Thanks, Adam

Sharon_Cutter · August 2013

Consider fixing your data so that all data is consistently in the same case. Having to uppercase strings in order to do comparisons will only waste cpu and possibly decrease the effectiveness of encoding and compression. Vertica's sweet spot is fast queries - don't degrade the performance with your data model.

Nicholas_Chamma_1 · October 2013

Hey Adam, Would setting colStrength = secondary cover Shibashis's original use case? Nick

We're Moving!

Create My New Community Account Now

How to compare two columns ignoring Case (Upper or Lower case)

Comments

Leave a Comment