啃食Oracle：字符串的猛操作验证数据(考试成绩)，substr,instr,to_number

说Oracle某表中有一个字段score(成绩)，借用with模拟students（学生）表情况如下

With students as(Select '1' kh,'语文:125,数学:124,外语:130,综合:211,总分:590' score from dual unionSelect '2' kh,'语文:112,数学:121,外语:141,综合:205,总分:579' score from dual unionSelect '3' kh,'语文:131,数学:86,外语:99,综合:193,总分:509,加分总分:514' score from dual unionSelect '4' kh,'语文:78,数学:38,外语:45,综合:129,总分:291,舞蹈:225.5' score from dual unionSelect '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 unionSelect '2' kh,'语文:112,数学:121,外语:141,综合:205,总分:579' score from dual unionSelect '3' kh,'语文:131,数学:86,外语:99,综合:193,总分:509,加分总分:514' score from dual unionSelect '4' kh,'语文:78,数学:38,外语:45,综合:129,总分:291,舞蹈:225.5' score from dual unionSelect '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)) fs1from students;

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

With students as(Select '1' kh,'语文:125,数学:124,外语:130,综合:211,总分:590' score from dual unionSelect '2' kh,'语文:112,数学:121,外语:141,综合:205,总分:579' score from dual unionSelect '3' kh,'语文:131,数学:86,外语:99,综合:193,总分:509,加分总分:514' score from dual unionSelect '4' kh,'语文:78,数学:38,外语:45,综合:129,总分:291,舞蹈:225.5' score from dual unionSelect '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 unionSelect '2' kh,'语文:112,数学:121,外语:141,综合:205,总分:579' score from dual unionSelect '3' kh,'语文:131,数学:86,外语:99,综合:193,总分:509,加分总分:514' score from dual unionSelect '4' kh,'语文:78,数学:38,外语:45,综合:129,总分:291,舞蹈:225.5' score from dual unionSelect '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 unionSelect '2' kh,'语文:112,数学:121,外语:141,综合:205,总分:579' score from dual unionSelect '3' kh,'语文:131,数学:86,外语:99,综合:193,总分:509,加分总分:514' score from dual unionSelect '4' kh,'语文:78,数学:38,外语:45,综合:129,总分:291,舞蹈:225.5' score from dual unionSelect '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)) fs5from students) aa where fs1+fs2+fs3+fs4=fs5;

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

With students as(Select '1' kh,'语文:125,数学:124,外语:130,综合:211,总分:590' score from dual unionSelect '2' kh,'语文:112,数学:121,外语:141,综合:205,总分:579' score from dual unionSelect '3' kh,'语文:131,数学:86,外语:99,综合:193,总分:509,加分总分:514' score from dual unionSelect '4' kh,'语文:78,数学:38,外语:45,综合:129,总分:291,舞蹈:225.5' score from dual unionSelect '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)) fs5from students) aa where fs1+fs2+fs3+fs4<>fs5;

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

With students as(Select '1' kh,'语文:125,数学:124,外语:130,综合:211,总分:590' score from dual unionSelect '2' kh,'语文:112,数学:121,外语:141,综合:205,总分:579' score from dual unionSelect '3' kh,'语文:131,数学:86,外语:99,综合:193,总分:509,加分总分:514' score from dual unionSelect '4' kh,'语文:78,数学:38,外语:45,综合:129,总分:291,舞蹈:225.5' score from dual unionSelect '4' kh,'语文:78,数学:38,外语:45,综合:129,总分:290,加分总分:295,舞蹈:225.5' score from dual unionSelect '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 thento_number(substr(score,instr(score,'加分总分',1,1)+5,instr(score||',',',',instr(score,'加分总分',1,1),1)-instr(score,'加分总分',1,1)-5))else null end fs6from students;

总结：以上就是Oracle中针对字符串的一个案例操作，笔者通过观察问题、思考方法、实现过程、扩展过程来和读者共同解决问题，目的是让读者也经历一次思考过程。达到熟练使用各种字符串函数的目的！