Select dataforjy from fdm_dm.dmp_plsadm_tradeinfo_m_20180227_mix where dataforjy like '%#____' or dataforjy like '%_#___' or dataforjy like '%__#__' or dataforjy like '%___#_' or dataforjy like '%____#'
select * from fdm_dm.dmp_plsadm_tradeinfo_m_20180227_mix where substr(lpad(paystat24month,24,'9'),-5) rlike '#'
RLIKE的其他使用,比如:
hive> select 1 from lxw_dual where 'footbar’ rlike'^f.*r$’;
> 1
注意:判断一个字符串是否全为数字:
hive> select 1 from lxw_dual where '123456' rlike'^\\d+$';
> 1
hive> select 1 from lxw_dual where '123456aa' rlike'^\\d+$';
Not like表示不包含的匹配,和like相反,但是用法不是Anot like B,而是not A like B .
hive> select not 'abcde' like '%c%e' from fdm_dm.dmp_plsadm_tradeinfo_m_20180227_mix hive > false
同理NOT RLIKE 的使用,也是NOT A RLIKEB
select PAYSTAT24MONTH from fdm_dm.dmp_plsadm_tradeinfo_m_20180227_mix where not substr(PAYSTAT24MONTH,length(PAYSTAT24MONTH)-4) rlike '#'