The find_in_set() UDF is a safer choice for doing a search for that value, as %239% could also match 2390, which has a different meaning in Omniture logs.
On Jan 4, 2012, at 8:46 AM, "Bejoy Ks" <bejoy...@yahoo.com<mailto:bejoy...@yahoo.com>> wrote: Hi Dave If I get your requirement correct, you need to load data into video_plays_for_sept table FROM omniture table only if omniture.event_list contain the string 239. Try the following query, it should work fine. INSERT OVERWRITE TABLE video_plays_for_sept SELECT concat(visid_high, visid_low), geo_city, geo_country, geo_region FROM omniture WHERE event_list LIKE ‘%239%’; Hope it helps!.. Regards, Bejoy.K.S ________________________________ From: Dave Houston <r...@crankyadmin.net<mailto:r...@crankyadmin.net>> To: user@hive.apache.org<mailto:user@hive.apache.org> Sent: Wednesday, January 4, 2012 6:41 PM Subject: Insert based on whether string contains Hi there, i have a string that has '239, 236, 232, 934' (not always in that order) and want to insert into another table if 239 is in the string. INSERT OVERWRITE TABLE video_plays_for_sept SELECT concat(visid_high, visid_low), geo_city, geo_country, geo_region from omniture where regexp_extract(event_list, '\d+') = "239"; is that I have at the minute but always returns 0 Rows loaded to video_plays_for_sept Many thanks Dave Houston r...@crankyadmin.net<mailto:r...@crankyadmin.net>
