X-Notes > Hive > 内置函数 > 字符串函数 > 查找、替换、子串和正则

查找、替换、子串和正则

查找

in_file(string str, string filename)
- 从文件（支持本地文件系统和 HDFS ）中查找目标字符串
- 如果能匹配到，则返回 true ；否则返回 false
- 必需整行匹配
- ```
!cat /root/test.txt;
!hdfs dfs -cat /test/test.txt;
select
    in_file('abc', '/root/test.txt'),
    in_file('ef', 'hdfs:/test/test.txt');
```
instr(string str, string substr)
- 在字符串中查找子串，返回第一次匹配到的首个字符的 index（从 1 开始）
- 如果匹配不到，则返回 0
- 若有一个参数为 NULL ，返回 NULL
- ```
select instr('cat,dog,cat', 'cat'), instr('cat,dog,cat', 'dog');
```

locate(string substr, string str[, int pos])

类似 instr(string str, string substr)
如果加了第三个参数 pos ，则从 pos 这个位置开始查找

select
    locate('cat', 'cat,dog,cat'), locate('dog', 'cat,dog,cat'),
    locate('cat', 'cat,dog,cat', 6), locate('dog', 'cat,dog,cat', 6);

field(val T,val1 T,val2 T,val3 T,...)

从 val1,val2,val3,… 中找出 val 的第一个位置
以下两种情况，返回 0 ：
- 找不到
- val 为 NULL

select
    field('apple', 'apple', 'banana', 'peach', 'pear'),
    field('apple', 'apple', 'banana', 'peach', 'pear', 'apple'),
    field('apple', 'banana', 'peach', 'pear'),
    field('apple', NULL, 'apple', 'banana', 'peach', 'pear'),
    field(NULL, 'apple', 'banana', 'peach', 'pear'),
    field(NULL, 'apple', 'banana', 'peach', 'pear', NULL);

find_in_set(string str, string strList)

从 strList 中找出 str 的第一个位置
以下两种情况，返回 0 ：
- 未找到
- str 中包含逗号（ strList 是一个用逗号隔开的字符串）
任一参数为 NULL ，则返回 NULL

select
    find_in_set('apple', 'apple,banana,peach,pear'),
    find_in_set('apple', 'apple,banana,peach,pear,apple'),
    find_in_set('apple', 'banana,peach,pear'),
    find_in_set('apple,', 'apple,banana,peach,pear'),
    find_in_set(NULL, 'apple,banana,peach,pear'),
    find_in_set('apple', NULL);

036

like

模糊查找
支持 SQL 通配符



drop table if exists test.test;
create table IF NOT EXISTS test.test(
    animals string
);
insert into test.test values
    ('ant'),('bear'),('cat'),('dog'),('eagle'),('fox'),('goat'),
    ('horse'),('impala'),('jellyfish'),('koala'),('lion'),('mouse'),('numbat'),
    ('oyster'),('penguin'),('quail'),('rabbit'),('shark'),('tiger'),
    ('unicorn'),('viper'),('wolf'),('xerus'),('yak'),('zebra');

select 'Hello World!' like '%lo%';
select * from test.test
    where animals like 'a%';
select * from test.test
    where animals like '_a%';

rlike 或 regexp

上面 like 的正则版本

select 'Hello World!' rlike '.*lo.*';
select * from test.test
    where animals rlike '^a.*$';
select * from test.test
    where animals rlike '^.{1}a.*$';
select * from test.test
    where animals rlike '^.{1}[i|u].*$';

036a

替换

replace(string A, string OLD, string NEW)
- 将字符串 A 中的全部 OLD 子串（不重叠）替换为 NEW
- 有任一个参数为 NULL ，返回 NULL
- ```
select replace('cat,dog,cat', 'cat', 'kitty');
```

regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)

用 REPLACEMENT 替换 INITIAL_STRING 中所有匹配 PATTERN 的子字符串

SELECT
    regexp_replace('a1b2c3d4', '([a-zA-Z]\\d)', 'A0'),
    regexp_replace('foothebar,fooabar', 'foo(.*?)(bar)', 'foobar'),
    regexp_replace('我1不23要456数7890字', '[0-9]+', '');

translate(string|char|varchar input, string|char|varchar from, string|char|varchar to)
- 类似上面的 replace(string A, string OLD, string NEW)
- 但不能超过 from 字符串的长度
- ```
select
    translate('cat,dog,cat', 'dog', 'puppy'),
    translate('cat,dog,cat', 'dog', 'pet'),
    translate('cat,dog,cat', 'dog', 'ab');
```

子串

substr(string|binary A, int start) 或 substring(string|binary A, int start)

从第 start 个字符开始，截取剩余的字符串

select
    substr('cat,dog,cat', 5), substring('cat,dog,cat', 9),
    substr('cat,dog,cat', -5), substring('cat,dog,cat', -9);

substr(string|binary A, int start, int len) 或 substring(string|binary A, int start, int len)

与上面一样，但指定截取的长度

select
    substr('cat,dog,cat', 5, 3), substring('cat,dog,cat', 9, 2),
    substr('cat,dog,cat', -5, 3), substring('cat,dog,cat', -9, 2);

substring_index(string A, string delim, int count)
- 以指定分隔符切分字符串，正数返回左侧的子串，负数返回右侧的子串
  - delim 为分隔符
  - count 为指定第一个分隔符，正数为从左数，负数为从右数
- ```
select
    substring_index('cat,dog,cat', ',', 1),
    substring_index('cat,dog,cat', ',', -2);
```

其他正则

regexp_extract(string subject, string pattern[, int index])

提取与正则表达式匹配的组
index 表示从返回结果中提取其中一部分，缺省是 1 ，参考
- 0 ，表示把整个结果全部返回
- 1 ，表示返回正则表达式中第一个() 对应的内容
- 依此类推
注意： \s 将匹配字母 s； \\s 是匹配任意空格字符

SELECT
    regexp_extract('a1b2c3d4', '([a-zA-Z]\\d)'),
    regexp_extract('a1b2c3d4', '([a-zA-Z]\\d)', 0),
    regexp_extract('a1b2c3d4', '([a-zA-Z]\\d)', 1),
    -- regexp_extract('a1b2c3d4', '([a-zA-Z]\\d)', 2), -- No group 2
    regexp_extract('foothebar', 'foo(.*?)(bar)', 0),
    regexp_extract('foothebar', 'foo(.*?)(bar)', 1),
    regexp_extract('foothebar', 'foo(.*?)(bar)', 2),
    -- regexp_extract('foothebar', 'foo(.*?)(bar)', 3), -- No group 3
    regexp_extract('hitdecisiondlist','(i)(.*?)(e)',0),
    regexp_extract('hitdecisiondlist','(i)(.*?)(e)',1),
    regexp_extract('hitdecisiondlist','(i)(.*?)(e)',2),
    regexp_extract('hitdecisiondlist','(i)(.*?)(e)',3);

037a