啃食Oracle：字符串的猛操作验证数据substr,instr,to_number（substr函数）

iseeyu4年前 (2022-10-13)运营百科270

吞食Oracle：数组的猛操作方式校正统计数据(笔试战绩)，substr,instr,to_number

说Oracle某表中有两个表头score(战绩)，转作with演示students（小学生）表情形如下表所示

With students as
(Select 1 kh,外语教学:125,微积分:124,外语教学:130,综合性:211,总战绩:590 score from dual union
Select 2 kh,外语教学:112,微积分:121,外语教学:141,综合性:205,总战绩:579 score from dual union
Select 3 kh,外语教学:131,微积分:86,外语教学:99,综合性:193,总战绩:509,破格总战绩:514 score from dual union
Select 4 kh,外语教学:78,微积分:38,外语教学:45,综合性:129,总战绩:291,现代舞:225.5 score from dual union
Select 5 kh,外语教学:113,微积分:116,外语教学:111,综合性:262,总战绩:602 score from dual)

明确要求来了，此表事实上由100圆觉以内，不考量操控性等情形下，请写下校正战绩数组中外语教学+微积分+外语教学+综合性=/≠总战绩的SQL句子，未知外语教学、微积分、外语教学、综合性、总战绩是要的且次序是一般来说的。

预测市场需求操作过程：

1）直言这是笔试统计数据，操作方式者不安心精确性，须要别人来帮助奇偶校验；

2）有两个两极化的相互关系是外语教学+微积分+外语教学+综合性=总战绩；

3）扩充观念：没让我排序破格总战绩，为的是避免再度麻烦事我，或是等着他来麻烦事我，我也把破格的事也处置下；

4）是不是除了现代舞，这是甚么分，不清楚啊，先无论它了，或是把她们撷取出；

5）我真的下面的范例不全，估算除了外语教学:131,微积分:86,外语教学:99,综合性:193,总战绩:509,破格总战绩:514,现代舞225.5的情形，统计数据范例估算是少了此种情形，历经沟通交流佐证了本栏的设想。

思考操作过程：

1）肯定得是使用substr撷取数组；

2）定位外语教学、微积分、外语教学、综合性、总战绩，那么这几个的次序是不是一般来说的呢？下面说了是一般来说的，一般来说的干法就是找冒号的位置，那就转作instr函数找冒号的位置；

3）奇偶校验单科战绩和总战绩的关系，得用到数组转数字函数to_number。

实现操作过程：

1）把各个单项战绩和总战绩弄出，事情就成了，先把外语教学弄出，别的可以仿制，开始你的操作方式：

函数Substr（score，外语教学战绩开始的位置，撷取长度）就能撷取到外语教学战绩，问题是如果定位开始位置和排序长度：

1-1））外语教学排在战绩项第两个，那么第两个冒号的位置 instr(score,:,1,1)+1就是外语教学战绩的开始位置。这个函数前面两个参数是父串和子串；第3个参数1，是从甚么位置开始找，根据预测每次肯定都是从1开始找；第4个参数是说找到第几个子串，外语教学是第两个所以写1，微积分是第二个那就是2，类推；这样才只是找到冒号的位置，由于分数从冒号后第一位开始的，所以位置还要加1，最后的定位表达式为：instr(score,:,1,1)+1；

1-2））长度问题，从给出的范例统计数据可以看出外语教学战绩最多3位数，除了2位数，那就一定会有1位数，比如0分，所以使用定长撷取肯定不行，这个长度也得通过排序，有了冒号看看除了啥可以借助的，嗯，逗号。逗号的位置-冒号的位置-逗号本身的占位就是长度，最后的长度排序表达式为：instr(score,,,1,1)-instr(score,:,1,1)-1

1-3））外语教学战绩的表达式为：to_number(substr(score,instr(score,:,1,1)+1,instr(score,,,1,1)-instr(score,:,1,1)-1))；

看看整体句子和执行效果：

With students as
(Select 1 kh,外语教学:125,微积分:124,外语教学:130,综合性:211,总战绩:590 score from dual union
Select 2 kh,外语教学:112,微积分:121,外语教学:141,综合性:205,总战绩:579 score from dual union
Select 3 kh,外语教学:131,微积分:86,外语教学:99,综合性:193,总战绩:509,破格总战绩:514 score from dual union
Select 4 kh,外语教学:78,微积分:38,外语教学:45,综合性:129,总战绩:291,现代舞:225.5 score from dual union
Select 5 kh,外语教学:113,微积分:116,外语教学:111,综合性:262,总战绩:602 score from dual)
select kh,to_number(substr(score,instr(score,:,1,1)+1,instr(score,,,1,1)-instr(score,:,1,1)-1)) fs1
from students;

2）达到理想效果，后面的战绩项目都效仿一下：

With students as
(Select 1 kh,外语教学:125,微积分:124,外语教学:130,综合性:211,总战绩:590 score from dual union
Select 2 kh,外语教学:112,微积分:121,外语教学:141,综合性:205,总战绩:579 score from dual union
Select 3 kh,外语教学:131,微积分:86,外语教学:99,综合性:193,总战绩:509,破格总战绩:514 score from dual union
Select 4 kh,外语教学:78,微积分:38,外语教学:45,综合性:129,总战绩:291,现代舞:225.5 score from dual union
Select 5 kh,外语教学:113,微积分:116,外语教学:111,综合性:262,总战绩:602 score from dual)
select kh,to_number(substr(score,instr(score,:,1,1)+1,instr(score,,,1,1)-instr(score,:,1,1)-1)) fs1,--外语教学
to_number(substr(score,instr(score,:,1,2)+1,instr(score,,,1,2)-instr(score,:,1,2)-1)) fs2,--微积分
to_number(substr(score,instr(score,:,1,3)+1,instr(score,,,1,3)-instr(score,:,1,3)-1)) fs3,--外语教学
to_number(substr(score,instr(score,:,1,4)+1,instr(score,,,1,4)-instr(score,:,1,4)-1)) fs4,--综合性
to_number(substr(score,instr(score,:,1,5)+1,instr(score,,,1,5)-instr(score,:,1,5)-1)) fs5--总战绩
from students;

3）甚么情形，总战绩上是不是有空的，分数是存在的，哪里出问题了呢？

substr(score,instr(score,:,1,5)+1,instr(score,,,1,5)-instr(score,:,1,5)-1)，预测一下问题，针对冒号的定位是没问题的，问题出在逗号的定位上，案例统计数据中考号为1，2，5总战绩后面没有逗号，其它是有的，也就是说最后两个战绩项目后面都是没有逗号的。想啊想（真实情形下要比这复杂，本栏考量很久），真笨，缺两个就给末尾补两个呗，所以在定位逗号的时候给score补充两个逗号 score||,不就解决了吗。

With students as
(Select 1 kh,外语教学:125,微积分:124,外语教学:130,综合性:211,总战绩:590 score from dual union
Select 2 kh,外语教学:112,微积分:121,外语教学:141,综合性:205,总战绩:579 score from dual union
Select 3 kh,外语教学:131,微积分:86,外语教学:99,综合性:193,总战绩:509,破格总战绩:514 score from dual union
Select 4 kh,外语教学:78,微积分:38,外语教学:45,综合性:129,总战绩:291,现代舞:225.5 score from dual union
Select 5 kh,外语教学:113,微积分:116,外语教学:111,综合性:262,总战绩:602 score from dual)
select kh,to_number(substr(score,instr(score,:,1,1)+1,instr(score,,,1,1)-instr(score,:,1,1)-1)) fs1,--外语教学
to_number(substr(score,instr(score,:,1,2)+1,instr(score,,,1,2)-instr(score,:,1,2)-1)) fs2,--微积分
to_number(substr(score,instr(score,:,1,3)+1,instr(score,,,1,3)-instr(score,:,1,3)-1)) fs3,--外语教学
to_number(substr(score,instr(score,:,1,4)+1,instr(score,,,1,4)-instr(score,:,1,4)-1)) fs4,--综合性
to_number(substr(score,instr(score,:,1,5)+1,instr(score||,,,,1,5)-instr(score,:,1,5)-1)) fs5--总战绩
from students;

4）基础统计数据都搞定了，完成工作吧。

4-1））查验相等的考生：

With students as
(Select 1 kh,外语教学:125,微积分:124,外语教学:130,综合性:211,总战绩:590 score from dual union
Select 2 kh,外语教学:112,微积分:121,外语教学:141,综合性:205,总战绩:579 score from dual union
Select 3 kh,外语教学:131,微积分:86,外语教学:99,综合性:193,总战绩:509,破格总战绩:514 score from dual union
Select 4 kh,外语教学:78,微积分:38,外语教学:45,综合性:129,总战绩:291,现代舞:225.5 score from dual union
Select 5 kh,外语教学:113,微积分:116,外语教学:111,综合性:262,总战绩:602 score from dual)
select * from
(select kh,to_number(substr(score,instr(score,:,1,1)+1,instr(score,,,1,1)-instr(score,:,1,1)-1)) fs1,
to_number(substr(score,instr(score,:,1,2)+1,instr(score,,,1,2)-instr(score,:,1,2)-1)) fs2,
to_number(substr(score,instr(score,:,1,3)+1,instr(score,,,1,3)-instr(score,:,1,3)-1)) fs3,
to_number(substr(score,instr(score,:,1,4)+1,instr(score,,,1,4)-instr(score,:,1,4)-1)) fs4,
to_number(substr(score,instr(score,:,1,5)+1,instr(score||,,,,1,5)-instr(score,:,1,5)-1)) fs5
from students) aa where fs1+fs2+fs3+fs4=fs5;

4-2））查验不等的考生：

With students as
(Select 1 kh,外语教学:125,微积分:124,外语教学:130,综合性:211,总战绩:590 score from dual union
Select 2 kh,外语教学:112,微积分:121,外语教学:141,综合性:205,总战绩:579 score from dual union
Select 3 kh,外语教学:131,微积分:86,外语教学:99,综合性:193,总战绩:509,破格总战绩:514 score from dual union
Select 4 kh,外语教学:78,微积分:38,外语教学:45,综合性:129,总战绩:291,现代舞:225.5 score from dual union
Select 5 kh,外语教学:113,微积分:116,外语教学:111,综合性:262,总战绩:602 score from dual)
select * from
(select kh,to_number(substr(score,instr(score,:,1,1)+1,instr(score,,,1,1)-instr(score,:,1,1)-1)) fs1,
to_number(substr(score,instr(score,:,1,2)+1,instr(score,,,1,2)-instr(score,:,1,2)-1)) fs2,
to_number(substr(score,instr(score,:,1,3)+1,instr(score,,,1,3)-instr(score,:,1,3)-1)) fs3,
to_number(substr(score,instr(score,:,1,4)+1,instr(score,,,1,4)-instr(score,:,1,4)-1)) fs4,
to_number(substr(score,instr(score,:,1,5)+1,instr(score||,,,,1,5)-instr(score,:,1,5)-1)) fs5
from students) aa where fs1+fs2+fs3+fs4<>fs5;

5)最后把破格总战绩也单独提取出，以备后需，这个破格总战绩是比较麻烦事的，因为不是要战绩项目，所以要判断是否存在，而且不能使用冒号和逗号的序号来定位，下面是实现句子，这个是最通用的提取分数的方法，其他战绩项目也可以使用这个方法。具体思考操作过程请独立完成：

With students as
(Select 1 kh,外语教学:125,微积分:124,外语教学:130,综合性:211,总战绩:590 score from dual union
Select 2 kh,外语教学:112,微积分:121,外语教学:141,综合性:205,总战绩:579 score from dual union
Select 3 kh,外语教学:131,微积分:86,外语教学:99,综合性:193,总战绩:509,破格总战绩:514 score from dual union
Select 4 kh,外语教学:78,微积分:38,外语教学:45,综合性:129,总战绩:291,现代舞:225.5 score from dual union
Select 4 kh,外语教学:78,微积分:38,外语教学:45,综合性:129,总战绩:290,破格总战绩:295,现代舞:225.5 score from dual union
Select 5 kh,外语教学:113,微积分:116,外语教学:111,综合性:262,总战绩:602 score from dual)
select kh,to_number(substr(score,instr(score,:,1,1)+1,instr(score,,,1,1)-instr(score,:,1,1)-1)) fs1,
to_number(substr(score,instr(score,:,1,2)+1,instr(score,,,1,2)-instr(score,:,1,2)-1)) fs2,
to_number(substr(score,instr(score,:,1,3)+1,instr(score,,,1,3)-instr(score,:,1,3)-1)) fs3,
to_number(substr(score,instr(score,:,1,4)+1,instr(score,,,1,4)-instr(score,:,1,4)-1)) fs4,
to_number(substr(score,instr(score,:,1,5)+1,instr(score||,,,,1,5)-instr(score,:,1,5)-1)) fs5,
case when instr(score,破格总战绩,1,1)>0 then
to_number(substr(score,instr(score,破格总战绩,1,1)+5,instr(score||,,,,instr(score,破格总战绩,1,1),1)-instr(score,破格总战绩,1,1)-5))
else null end fs6
from students;