记一次oracle解析字符串数组的心理历程

xieshouwen

已于 2024-09-06 15:06:24 修改

阅读量479

点赞数

分类专栏： SSIS 文章标签： sql 数据库 database

于 2022-05-23 11:49:34 首次发布

本文链接：https://blog.csdn.net/xieshouwen/article/details/124882014

版权

SSIS 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

在一次数据库操作中，作者面临解析逗号分隔的字符串数组并进行统计的任务。初始自信满满，但在实际操作中遇到了Oracle中没有split函数、字符串格式不规范等问题，包括全角逗号、制表符、多余小数点等。通过百度搜索找到fn_split函数，并逐步解决各种异常，最终成功解析并计算数值。然而，作者认为可能还有更复杂的数据等待挑战。

摘要由CSDN通过智能技术生成

事件背景：

数据库表中躺着这么一堆实验记录的数据，需要自动解析每个测量点的值，并统计出其最大值，最小值，平均值，标准差。。。。

心理历程一：小菜一碟

看到这个需求后，这种使用逗号分开的字符串解析还不是手拿把掐吗。立马夸下海口，分分钟给你搞定。连上数据库后，立马开始撸SQL。刚写了select * from xxx ，就一直在想Oracle使用字符分拆的函数叫什么来着？好像没有类似split的函数啊？这个得百度一下了。

由于我刚才的话，来求助的哥们就在旁边等我搞定。刚写了半句SQL，就找度娘多少有点抹不开面。就跟他说你先回去，我一会搞定给你。

心理历程二：度娘真香

百度后，找到了一个拆分字符串的函数fn_split 。

CREATE OR REPLACE FUNCTION fn_split(p_str       IN VARCHAR2,--待分割字符串
                                    p_delimiter IN VARCHAR2)--分割标记符
 RETURN ty_str_split
  PIPELINED IS
  j    INT := 0;
  i    INT := 1;
  len  INT := 0;
  len1 INT := 0;
  str  VARCHAR2(4000);
BEGIN
  len  := LENGTH(p_str);
  len1 := LENGTH(p_delimiter);
  WHILE j < len LOOP
    j := INSTR(p_str, p_delimiter, i);
    IF j = 0 THEN
      j   := len;
      str := SUBSTR(p_str, i);
      PIPE ROW(str);
      IF i >= len THEN
        EXIT;
      END IF;
    ELSE
      str := SUBSTR(p_str, i, j - i);
      i   := j + len1;
      PIPE ROW(str);
    END IF;
  END LOOP;
  RETURN;
END fn_split;

嗯，度娘真香。心想这下稳了。立马开始敲代码测试下。

select *
  from table(etcuser.fn_split('160.5,164.5,166.3,160.9,172.7,168.8,169.4',
                              ','))

结果如下：

嗯，这个结果正是我想要的。但是-------------------你以为就这样结束了吗？

心理历程三：数据教你做人

计算最大值，开始撸SQL

select (select max(to_number(column_value))
          from table(fn_split(t1.actual, ','))) maxVal
  from xxxxx t1

弹出如下错误

这个错误在我意料之中，这么多数据总有不按规矩录入的嘛。

1、逗号有的半角有的全角，替换之

2、字符串中存在制表符的，如TAB、回车，替换之

2、把解析出的字符串中除数字、小数点的都干掉

select (select max(to_number(REGEXP_replace(column_value, '[^0-9\.]', '')))
          from table(fn_split(replace(replace(replace(t1.ACTUAL, chr(13), ','), chr(10), ','),'，',','),',')))) maxVal
  from xxxxx t1

心想，应该没什么错了吧。可运行后还是会报无效数字这个错，什么情况呢。

一番数据查找后，发现解析过后存在12.12.12.12这样的数据，居然解析出了多个小数点？？？

好嘛，手工录入的数据嘛。总避免不了差错的，再次修改如下：

select (select max(case when 
    regexp_count(REGEXP_replace(column_value,'[^0-9\.]',''),'\.') <= 1 
    then to_number(REGEXP_replace(column_value, '[^0-9\.]', ''))
    else null end)
          from table(fn_split(replace(replace(replace(t1.ACTUAL, chr(13), ','), chr(10), ','),'，',','),',')))) maxVal
  from xxxxx t1

再次运行，还是报错无效数字。我嫩爹，还有什么幺蛾子吗？

又是一番苦思冥想。最后猜测: 难道解析后只有小数点的存在？修改SQL如下：

select (select max(case when 
    regexp_count(REGEXP_replace(column_value,'[^0-9\.]',''),'\.') <= 1  and
    length(replace(REGEXP_replace(column_value,'[^0-9\.]',''),'.','')) > 0 
    then to_number(REGEXP_replace(column_value, '[^0-9\.]', ''))
    else null end)
          from table(fn_split(replace(replace(replace(t1.ACTUAL, chr(13), ','), chr(10), ','),'，',','),',')))) maxVal
  from xxxxx t1

最后，终于没有再报错。理论上这个应该可以解析出所有的正数值的计算了

但是，我想这也可能是还没遇到更加神奇的数据而已。