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

Switch to Threaded View
Hive >> mail # user >> Issues with Hive 'Like" and multiple '%' wildcard in a pattern


Copy link to this message
-
Re: Issues with Hive 'Like" and multiple '%' wildcard in a pattern
Hi Mark,
i'm running v0.80 too and multiple '%'s work as expected for me. so. we're
gonna need a see a definitive test case from you.

show your full string and show where the like clause fails to match.

thanks,
Stephen.
PS here's my test:

hisql>select city from junk;
+------------------+
| city             |
+------------------+
| West Haven       |
| West Haven       |
| West Haven       |
| West Haven       |
| West Jordan      |
| West Chester     |
| Westminster      |
| West Chester     |
| West Chester     |
| Westlake         |
| West Chester     |
| West Chester     |
| West Chester     |
| Weston           |
| West Covina      |
| West Chester     |
| West Chester     |
| West Hollywood   |
| West Des Moines  |
| West Covina      |
| Westland         |
| West Valley City |
| West Valley City |
| West Valley City |
| West Valley City |
| Westminster      |
| West Memphis     |
| West Memphis     |
+------------------+
28 affected

hisql>select city from junk where city like '%West%v%';
+-------------+
| city        |
+-------------+
| West Haven  |
| West Haven  |
| West Haven  |
| West Haven  |
| West Covina |
| West Covina |
+-------------+
6 affected

hisql>select city from junk where city like '%West%v%a%';
+-------------+
| city        |
+-------------+
| West Covina |
| West Covina |
+-------------+
2 affected
On Wed, Jun 12, 2013 at 10:26 AM, Sunderlin, Mark <
[EMAIL PROTECTED]> wrote:

>  We are using Hive 0.80.****
>
> ** **
>
> ---****
>
> *Mark E. Sunderlin***
>
> *Solutions Architect **|AOL Networks** BDM*
>
> P: 703-265-6935     |C: 540-327-6222 | AIM: MESunderlin****
>
> 22000 AOL Way | Dulles, VA | 20166****
>
> ** **
>
> *From:* Stephen Sprague [mailto:[EMAIL PROTECTED]]
> *Sent:* Wednesday, June 12, 2013 1:00 PM
> *To:* [EMAIL PROTECTED]
> *Subject:* Re: Issues with Hive 'Like" and multiple '%' wildcard in a
> pattern****
>
> ** **
>
> that seems pretty hard to believe. what version of hive are you using?****
>
> ** **
>
> On Wed, Jun 12, 2013 at 6:27 AM, Sunderlin, Mark <
> [EMAIL PROTECTED]> wrote:****
>
> This seems to work just fine in other SQLs, but doesn't seem work in hive.
>
> I need to have several wild card characters in my 'like' clause as follows.
>
> In other SQLs, I want:  ....  where page_url_query like '%?icid=main%dl%'
>
> But in Hive that doesn't match.  I have several work arounds.  I can write
> it as:
>
>  where (page_url_query like '%?icid=main%')  and (page_url_query like
> '%dl%')
>
> or, I can just use something like where
> length(regexp_extract(page__url_query, '.*\?cdid-main.*dl.*',0) <> 0
>
> But more important is two or more '%' wild cards in a Hive like expression
> a known issue?
>
> ---
> Mark E. Sunderlin
> Solutions Architect   |AOL NETWORKS BDM
> P: 703-265-6935       |C: 540-327-6222 | AIM: MESunderlin
> 22000 AOL Way,  Dulles, VA  20166
>
> ****
>
> ** **
>