Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Re: Difference between like %A% and %a%


Copy link to this message
-
Re: Difference between like %A% and %a%
Postgres/Vertica and their ilk have ILIKE which is a case-insensitive
version of LIKE, in addition to the case-sensitive LIKE. Works well having
both.

Cheers,
Anthony
On Fri, May 24, 2013 at 8:58 AM, Edward Capriolo <[EMAIL PROTECTED]>wrote:

> It is not as simple of a problem as you think. Mysql has the same problem
> just most everyone uses a default charset and comparator.
>
> http://www.bluebox.net/about/blog/2009/07/mysql_encoding/
>
> You do you account for foreign characters like the a~ etc. is that > then
> A and less then <
>
>
> On Fri, May 24, 2013 at 11:41 AM, Dean Wampler <[EMAIL PROTECTED]>wrote:
>
>> If backwards compatibility wasn't an issue, the hive code that implements
>> LIKE could be changed to convert the fields and LIKE strings to lower case
>> before comparing ;) Of course, there is overhead doing that.
>>
>> On Fri, May 24, 2013 at 9:50 AM, Edward Capriolo <[EMAIL PROTECTED]>wrote:
>>
>>> Also I am thinking that the rlike is based on regex and can be told to
>>> do case insensitive matching.
>>>
>>>
>>> On Fri, May 24, 2013 at 9:16 AM, Dean Wampler <[EMAIL PROTECTED]>wrote:
>>>
>>>> Hortonworks has announced plans to make Hive more SQL compliant. I
>>>> suspect bugs like this will be addressed sooner or later. It will be
>>>> necessary to handle backwards compatibility, but that could be handled with
>>>> a hive property that enables one or the other behaviors.
>>>>
>>>> On Fri, May 24, 2013 at 8:07 AM, John Omernik <[EMAIL PROTECTED]> wrote:
>>>>
>>>>> I have mentioned this before, and I think this a big miss by the Hive
>>>>> team.  Like, by default in many SQL RDBMS (like MSSQL or MYSQL)  is not
>>>>> case sensitive. Thus when you have new users moving over to Hive, if they
>>>>> see a command like "like" they will assume similarity (like many other SQL
>>>>> like qualities) and thus false negatives may ensue.  Even though it's
>>>>> different by default (I am ok with this ... I guess, my personal preference
>>>>> is that it matches the defaults on other systems, and outside of that
>>>>> (which I am, in in the end fine with, just grumbly :) ) give us the ability
>>>>> to set that behavior in the hive-site.xml.  That way when an org realizes
>>>>> that it is different, and their users are all getting false negatives, they
>>>>> can just update the hive-site and fix the problem rather than have to
>>>>> include it in training that may or may not work.  I've added this comment
>>>>> to https://issues.apache.org/jira/browse/HIVE-4070#comment-13666278 for fun. :)
>>>>>
>>>>> Please? :)
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Fri, May 24, 2013 at 7:53 AM, Dean Wampler <[EMAIL PROTECTED]>wrote:
>>>>>
>>>>>> Your where clause looks at the abbreviation, requiring 'A', not the
>>>>>> state name. You got the correct answer.
>>>>>>
>>>>>>
>>>>>> On Fri, May 24, 2013 at 6:21 AM, Sai Sai <[EMAIL PROTECTED]> wrote:
>>>>>>
>>>>>>> But it should get more results for this:
>>>>>>>
>>>>>>> %a%
>>>>>>>
>>>>>>> than for
>>>>>>>
>>>>>>> %A%
>>>>>>>
>>>>>>> Please let me know if i am missing something.
>>>>>>> Thanks
>>>>>>> Sai
>>>>>>>
>>>>>>>
>>>>>>>    ------------------------------
>>>>>>>  *From:* Jov <[EMAIL PROTECTED]>
>>>>>>> *To:* [EMAIL PROTECTED]; Sai Sai <[EMAIL PROTECTED]>
>>>>>>> *Sent:* Friday, 24 May 2013 4:39 PM
>>>>>>> *Subject:* Re: Difference between like %A% and %a%
>>>>>>>
>>>>>>>
>>>>>>> 2013/5/24 Sai Sai <[EMAIL PROTECTED]>
>>>>>>>
>>>>>>> abbreviation l
>>>>>>>
>>>>>>>
>>>>>>> unlike MySQL, string in Hive is case sensitive,so '%A%' is not equal
>>>>>>> with '%a%'.
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Jov
>>>>>>> blog: http:amutu.com/blog <http://amutu.com/blog>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Dean Wampler, Ph.D.
>>>>>> @deanwampler
>>>>>> http://polyglotprogramming.com
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Dean Wampler, Ph.D.
>>>> @deanwampler
>>>> http://polyglotprogramming.com
>>>
>>>
>>>
>>
>>
>> --
>> Dean Wampler, Ph.D.
>> @deanwampler
>> http://polyglotprogramming.com