使用hiveSQL分离字符串中的字符和数字

0-需求

如果数据中存在字符和数字混在一起的情况且放在一列中，此时需要将其中的数字数据和字符数据分开，分别单独成为一列，应该怎么做？

如下数据：

FLINK434354
HADOOP67889
HBASE500019
KAFKA15999
SQOOP13332
HELLO57000
SPARK13000

需要将中文名称和数字单独提取出来，得到如下结果

FLINK	434354
HADOOP	67889
HBASE	500019
KAFKA	15999
SQOOP	13332
HELLO	57000
SPARK	13000

1-数据分析

（1）建表，插入数据

create table books (
data string
)
row format delimited fields terminated by ‘,’;

insert into books values
(“FLINK434354”),
(“HADOOP67889”),
(“HBASE500019”),
(“KAFKA15999”),
(“SQOOP13332”),
(“HELLO57000”),
(“SPARK13000”);

（2）分析

此问题的技巧点在于把不同字符替或数字替换成一个指定的特征字符或特征数字，这样的好处在于一个字符就可以代表所有的字符，一个特征数字就可以代表所有的数字，在具体删除的时候，我们就不需要逐一查找多个字符或多个数字了。上述的思想是解决此类问题的核心方法和要义。

根据上述思想:我们知道要把不同的字符或数字替换成一个特征字符或数字，因此我们想到用translate()函数。

第一步：转换为指定特征字符或特征数字

select data
,translate(lower(data),‘abcdefghijklmnopqrstuvwxyz’,repeat(’*’,26))
,translate(lower(data),‘0123456789’,repeat(’$’,10))
from books

这里使用了repeat()函数，表示重复指定个数的字符，展示如下：

select repeat(‘z’,26)
0: jdbc:hive2://10.9.4.117:10000> select repeat(‘z’,26);
±----------------------------±-+
| _c0 |
±----------------------------±-+
| zzzzzzzzzzzzzzzzzzzzzzzzzz |
±----------------------------±-+

计算结果如下：

FLINK434354 *****434354 flink$$$$$
HADOOP67889 ******67889 hadoop$ (后面和前面一样)
HBASE500019 *****500019 hbase$
KAFKA15999 *****15999 kafka$
SQOOP13332 *****13332 sqoop$
HELLO57000 *****57000 hello$
SPARK13000 *****13000 spark$

第二步：删除指定的特征字符，即是我们所求结果。

由于是删除指定的字符，因此我们采用repalce()函数或regexp_replace()进行删除,hive1.1版本只有regexp_replace()函数，hive1.2以后有replace()函数.具体SQL如下：

对于特殊的字符，使用replace()或regexp_replace()时候需要进行转义（//$,//*）

select data
,cast(regexp_replace(translate(lower(data),‘abcdefghijklmnopqrstuvwxyz’,repeat(’’,26)),’\’,’’) as bigint) as book_money
,regexp_replace(translate(lower(data),‘0123456789’,repeat(’$’,10)),’\$’,’’) as book_name
from books

最终计算结果如下：

FLINK434354 flink 434354
HADOOP67889 hadoop 67889
HBASE500019 hbase 500019
KAFKA15999 kafka 15999
SQOOP13332 sqoop 13332
HELLO57000 hello 57000
SPARK13000 spark 13000

2-总结

本文给出了一种利用HQL语言分离字符串中含有字符和数字的技巧和方法，主要使用regexp_replace()函数及translate()进行求解，求解的核心思想就是将不同的字符或数字替换成指定的特征字符，然后用regexp_replace()函数删除指定的特征字符，即可分离出字符及数字。

小扩展
本题思考：如何按照字符串中的数字进行排序？

如本题按照字符串中书的价格由高到低进行排序

HQL如下：

select data
from books
order by
cast(regexp_replace(translate(lower(data),‘abcdefghijklmnopqrstuvwxyz’,repeat(’’,26)),’\’,’’) as bigint) desc

结果如下：

---

OK
HBASE500019
FLINK434354
HADOOP67889
HELLO57000
KAFKA15999
SQOOP13332
SPARK13000