[HIVEQL] LIKE 和 RLIKE 之间的区别

LIKE是普通LIKE，RLIKE是高级LIKE，即适用于正则。

---

例0：

[查询sql]

select '123' rlike '^[0-9]+$','123abc' rlike '^[0-9]+$'

[结果]

 

123 RLIKE ^[0-9]+$	123abc RLIKE ^[0-9]+$
true	false

---

例1：

[查询sql]

SELECT  
    a,
    
    case 
        when a rlike '^[0-9]+$' then a
	    else '-'
	end col1,
	
	case
	    when a LIKE '[0-9]+' then a
	    else '-'
	end col2,
	
	case
	    when a LIKE '^[0-9]*$' then a
	    else '-'
	end col3
FROM
	(
		SELECT '123_sku' a
		
		union all
		
		SELECT '1sku' a
		
		union all
		
		SELECT 'sku' a
				
		union all
		
		SELECT '123' a
	)
	t

[结果]

a	col1	col2	col3
123_sku	-	-	-
1sku	-	-	-
sku	-	-	-
123	123	-	-

---

例2：

[查询sql]

SELECT  
    a,
    case 
        when a rlike '.*(ALICE|BEN).*' then a
	    else '-'
	end col1,
	case
	    when a LIKE '%ALICE%' then a
	    else '-'
	end col2,
	case
	    when a LIKE '%ALICE' then a
	    else '-'
	end col3
FROM
	(
		SELECT 'I LIKE ALICE AND BEN' a
		union all
		SELECT 'I LIKE ALICE AND CAT' a
	)
	t

[结果]

a	col1	col2	col3
I LIKE ALICE AND BEN	I LIKE ALICE AND BEN	I LIKE ALICE AND BEN	-
I LIKE ALICE AND CAT	I LIKE ALICE AND CAT	I LIKE ALICE AND CAT	-