如何查询VARCHAR2字段中的数字？

qiuling

有一张成绩表，列：成绩表ID，学号，课程号，学期代码，考试类型，成绩，学分，记录产生时间，UpdateTime，备注
其中成绩里面有字母，整数，浮点数。
字母成绩包括：A，A-，B+，B，B- 等等

SQL里面有个isnumerical函数可以判断数值还是字母，不过网上查过Oracle是没有这类函数的。

现在有两个需求：
1. 需要对成绩中的数字成绩和字母成绩进行区分判断加以整理，目前我的做法是直接在where语句中对每一个字母进行判断（用and）排除掉字母只剩下数字的成绩，我想问一下有没有比较方便的处理方式？

2. 我无法保证数字成绩的准确性，所以需要进行检查。首先排除掉成绩中的字母，然后需要对数字成绩中的小数点位数判断以便找出不寻常的成绩。比如小数点后面有3位就属于不正常成绩。由于成绩的字段类型是VARCHAR2（在MS-SQL里面是nvarchar），我如何判断小数点后面数字的位数？
select * from 成绩表
where 成绩 like '%.__'
和
select * from 成绩表
where charindex('.',reverse(成绩))-1=2

找出的结果都不太准确，因为我人工检索时看到过有“65.67”这样的成绩，但是用上述SQL语句无法查询出来。

zhm2815

你们的数据库版本是多少？如果是10g及以上，可以用正则表达式来实现。
请看例子：
WITH s AS (
   SELECT 1 AS ID,'a23' AS grade FROM dual
   UNION ALL
   SELECT 2 AS ID,'2a3.33' AS grade FROM dual
   UNION ALL
   SELECT 3 AS ID,'23.333' AS grade FROM dual
   UNION ALL
   SELECT 4 AS ID,'23' AS grade FROM dual
   UNION ALL
   SELECT 5 AS ID,'23.23' AS grade FROM dual
   UNION ALL
   SELECT 5 AS ID,'23.2a3' AS grade FROM dual
)

SELECT * FROM s
WHERE regexp_like(grade,'^[0-9]{1,}($|\.[0-9]{1,2}$)')

qiuling

实话说，数据在MS-SQL2000上面。要整理到Oracle 10g上面。
我试试看能不能做。

qiuling

字母成绩包括：A，A-，B+，B，B- 等等
整数成绩：0，单个整数（0~9），两位整数，三位整数(100分)；
浮点数成绩：5.5、5.6、5.55、5.65，55.5、55.6、55.55、55.56 等等

之前发现过有一个成绩为62.678，经查是导入错误，后业务员改为63分。
目的是想检查一下还有没有非正常成绩。

zhm2815

原帖由 qiuling 于 2012-5-3 16:17 发表
实话说，数据在MS-SQL2000上面。要整理到Oracle 10g上面。
我试试看能不能做。

要是10g oracle正则表达式很好用的。

qiuling

MS-SQL查询
select * from 成绩表
where IsNumeric (成绩)=0——可以查出所以该列中非数字成绩的记录；


Access查询
select * from 成绩表
where IsNumeric (成绩)<>true——查询结果同上；


区分开后就要考虑对数字成绩的判断。

liuxbgiant

二楼的修改为
WITH s AS (
   SELECT 1 AS ID,'a23' AS grade FROM dual
   UNION ALL
   SELECT 2 AS ID,'2a3.33' AS grade FROM dual
   UNION ALL
   SELECT 3 AS ID,'23.333' AS grade FROM dual
   UNION ALL
   SELECT 4 AS ID,'23' AS grade FROM dual
   UNION ALL
   SELECT 5 AS ID,'.23' AS grade FROM dual
   UNION ALL
   SELECT 5 AS ID,'23.2a3' AS grade FROM dual
)

SELECT * FROM s
WHERE regexp_like(grade,'^[0-9]{0,}($|\.[0-9]{1,2}$)')

zhm2815

原帖由 liuxbgiant 于 2012-5-3 23:42 发表
二楼的修改为
WITH s AS (
   SELECT 1 AS ID,'a23' AS grade FROM dual
   UNION ALL
   SELECT 2 AS ID,'2a3.33' AS grade FROM dual
   UNION ALL
   SELECT 3 AS ID,'23.333' AS grade FROM dual
   UNION ALL
  ...

请看结果!