SQL中按分隔符拆分字符串

一、Oracle数据库按分隔符拆分字符串

1,应用函数

REGEXP_SUBSTR

REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)

3,参数解释

  • __srcstr :需要进行正则处理的字符串
  • __pattern :进行匹配的正则表达式
  • __position :起始位置,从第几个字符开始正则表达式匹配(默认为1)
  • __occurrence :标识第几个匹配组,默认为1
  • __modifier :模式('i'不区分大小写进行检索;'c'区分大小写进行检索。默认为'c'。)
  • 4,测试SQL

    select regexp_substr('1,2,3','[^,]+',1,1) result from dual;
    

    运行结果:

    5,REGEXP_SUBSTR+CONNECT BY 使用

    按分隔符拆分字符串+CONNECT BY 动态参数
    测试SQL:

    SELECT 
    REGEXP_SUBSTR ('1,2,3,4,5', '[^,]+', 1,ROWNUM) 
    FROM dual 
    CONNECT BY ROWNUM <= LENGTH( '1,2,3,4,5' ) - LENGTH(regexp_replace('1,2,3,4,5', ',', '' )) + 1
    

    运行结果:

    二、hive数据库按分隔符拆分字符串

    1,split()函数

    (1)定义

    split()函数是用于切分数据,也就是将一串字符串切割成了一个数组

    (2)语法

    语法:split(string str, string pat)
    返回值:数组类型array

    (3)参数解释

    string str :待分割字符串
    string pat:分割符

    (4)测试

    测试SQL:

    select split ('wo,shi,xiao,ming',',');
    

    运行结果:

    ["wo","shi","xiao","ming"]
    

    2,explode函数

    (1)定义

    explode()函数是用于打散行的函数,将一行的数据拆分成一列

    (2)语法

    explode(array/map类型)
    select explode(array_col) as new_col from table_name

    (3)测试

    测试SQL:

    select explode(array("wo","shi","xiao","ming")) as word;
    

    运行结果:

    (4)explode函数的局限性

  • 不能关联原有的表中的其他字段
  • 不能与group by、cluster by、distribute by、sort by联用
  • 不能进行UDTF嵌套
  • 参数只能是两种类型
  • 一个select后面只能获得一个explode产生的视图,如果要显示多个列,则需要将多个视图合并。lateral view就是做这样的事的
  • 3,lateral view

    (1)定义

    Lateral View用于和UDTF函数(explode、split)结合来使用
    首先通过UDTF函数拆分成多行,再将多行结果组合成一个支持别名的虚拟表。虚拟表相当于再和主表关联, 从而达到添加“UDTF生成的字段“以外字段的目的, 即主表里的字段或者主表运算后的字段。
    主要解决在select使用UDTF做查询过程中,查询只能包含单个UDTF,不能包含其他字段、以及多个UDTF的问题
    

    (2)语法

    lateral view UDTF(expression) table_view as new_column;

    (3)参数解释

  • UDTF(expression):复合逻辑规则的UDTF函数,最常用的explode
  • table_view : 对应的虚拟表的表名
  • new_col: 虚拟表里存放的有效字段