在varchar字段中搜索特定范围的数值 [英] Searching a varchar field for numeric values of a certain range

问题描述

使用Oracle SQL Developer v3.2.20.09:

Using Oracle SQL Developer v3.2.20.09:

我有一个数据表，在所有其他数据中，我有一列所有数值结果(例如，RESULT_NUM)，但是由于它的存储和使用方式，它是一个varchar2字段.我需要提取所有体温代码(VT_CODE字段中的BT，TEMP，TEMPERATURE)的所有记录，其中result_num > 100(所有内容均在华氏温度范围内，因此仅对结果进行搜索就可以了)

I have a table of data where, among all the other data, I have a column of all numeric results (for examples' sake, RESULT_NUM) but due to the way it is stored and used, is a varchar2 field. I need to pull all the records for the Body Temperature Codes (BT, TEMP, TEMPERATURE in the VT_CODE field) where result_num > 100 (everything is in Fahrenheit, so searching on the result alone will work).

所以，我的简单声明是:

So, my simple statement is:

Select * from VITALS where VT_CODE in ('BT', 'TEMP', 'TEMPERATURE');

这把我踢回了所有体内温度记录，超过了2M.现在，我需要对其进行优化以获取超过100个的结果.

This kicks me back all of the body temp records, which is over 2M. Now I need to refine it to get results that are over over 100.

当我尝试添加和result_num > 100"时，出现错误，因为它是一个varchar字段，我正在尝试使用数字进行搜索.

When I try to add "and result_num > 100" I get an error because it is a varchar field I am trying to search with a number.

当我尝试添加"and result_num > '100'"时，由于它是一个字符值，因此执行时没有错误，但是显然返回的所有值都大于1，而不是100.

When I try to add "and result_num > '100'", it executes without error because it is a character value, but it returns everything greater than 1, not 100, which is everything, obviously.

请帮助.

推荐答案

尝试以下操作

and convert(int,result_num) > 100

或

and CAST(result_num as INTEGER) > 100

这篇关于在varchar字段中搜索特定范围的数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！