使用 rlike :

with your_data as (
select stack (8,
1,'calcium potassium iodine fluoride',
2,'potassium calcium',
3,'hydrochloride\\; calcium',
4,'iodine, calcium, fluoride',
5,'iodine calcium phosphate',
6,'calcium phosphate' ,
7,'IODINE',
8,'calcium PHOSPHATE iodine') as (med_code,med_name)
)

select * from your_data 
 where med_name rlike '(?i)calcium phosphate' --(?i) means case insensitive
    or med_name rlike '^(?i)iodine$'          --with string begin(`^`) and end(`$`) anchors
;

退货：

your_data.med_code  your_data.med_name  
5   iodine calcium phosphate    
6   calcium phosphate   
7   IODINE  
8   calcium PHOSPHATE iodine

这在 hive 里有效

你可以将文本与like进行比较，然后只比较列的长度，这将给出你所期望的上述结果

SELECT med_code, med_name
FROM med_table
WHERE (lower(med_name) LIKE '%calcium phosphate%' AND length(Column)=18 

OR WHERE (lower(med_name) LIKE '%iodine%' AND length(Column)= 7)

列的长度应该是like子句的字符串的长度检查语法就像刚才写的这个运行时还为字符串的组合添加了条件，比如'cadium phosphate iodium'和'lidium….'

必须用空字符串替换术语和逗号，然后修剪结果并检查长度是否为0：

SELECT med_code, med_name
FROM med_table
WHERE
  length(trim(
    regexp_replace(regexp_replace(regexp_replace(lower(med_name), 'calcium phosphate', ''), 'iodine', ''), ',', '')
  )) = 0

请参阅演示（对于mysql，区别在于 replace 而不是Hive regexp_replace ).
结果：

| med_code | med_name                 |
| -------- | ------------------------ |
| 5        | iodine calcium phosphate |
| 6        | calcium phosphate        |
| 7        | IODINE                   |
| 8        | calcium PHOSPHATE iodine |