hive instrt_hive instr 函数

instr(string str, string substr)

查找字符串str中子字符串substr出现的位置，如果查找失败将返回0，如果任一参数为Null将返回null，注意位置为从1开始的

示例：如果在一个表里面找出某些字段包含一些特殊字符的数据 如：

*

(

)

+

|

"

:

?

>

<

{

}

[

]

.

,

则代码如下

select cid,

last_name,

first_name

from

contact.t_customer_contact_list_total

where day='2017-06-22'

and

(

instr(first_name,'*')>0 or instr(last_name,'*')>0 or

instr(first_name,'(')>0 or instr(last_name,'(')>0 or

instr(first_name,')')>0 or instr(last_name,')')>0 or

instr(first_name,'_')>0 or instr(last_name,'_')>0 or

instr(first_name,'+')>0 or instr(last_name,'+')>0 or

instr(first_name,'"')>0 or instr(last_name,'"')>0 or

instr(first_name,':')>0 or instr(last_name,':')>0 or

instr(first_name,'?')>0 or instr(last_name,'?')>0 or

instr(first_name,'>')>0 or instr(last_name,'>')>0 or

instr(first_name,'0 or instr(last_name,'0 or

instr(first_name,'{')>0 or instr(last_name,'{')>0 or

instr(first_name,'}')>0 or instr(last_name,'}')>0 or

instr(first_name,'[')>0 or instr(last_name,'[')>0 or

instr(first_name,']')>0 or instr(last_name,']')>0 or

instr(first_name,'.')>0 or instr(last_name,'.')>0

)

limit 10

;