SQL查询提取数字及其小数变化

I have column with all kinds of numbers, I am specifically trying to extract numbers that have either

我有各种数字的专栏,我特意试图提取具有任何数字的数字

555.xx

555.x

The output should look like this

输出应该如下所示

IE I need an sql query that will return the rows that have just the number 555 with any decimal fraction from my column of arbitrary numbers.

IE我需要一个sql查询,它将返回具有任意数字列的任何小数部分的数字555的行。

4 个解决方案

#1

You can try LIKE statement

你可以尝试LIKE语句

WHERE Col LIKE '555.%'
OR Col = '555'

#2

As a fast approach I would do

作为一种快速的方法,我会这样做

CAST((ValueOfTable * 100.00) AS DECIMAL(18, 2))

my table name is Diagnosis and the column name is code, where should I add the table name and column name in this code ?

我的表名是Diagn,列名是代码,我应该在这个代码中添加表名和列名?

In your situation:

在你的情况下:

SELECT
CAST((code * 100.00) AS DECIMAL(18, 2)) 
FROM Diagnosis ;

I'm expecting this to be an integer. You can find out executing:

我期待这是一个整数。你可以找到执行:

\d Diagnosis ;

one of the output lines should look similar to

其中一条输出线看起来应该类似

 (...)
 code                       | integer                     | 
 (...)

#3

Assuming the column contains numbers (not as string/varchar), search for "number>=555 and number<556". This would give you 555, 555.01... etc.

假设该列包含数字(而不是字符串/ varchar),请搜索“number> = 555且number <556”。这会给你555,555.01 ......等

#4

If the CODE column is a varchar (which I understand it to be from your comments, but you might want clarify that in the question body itself), and can/does contain values that are not numbers, then you have to be very careful about using functions which only accept a number.

如果CODE列是varchar(我理解它来自你的评论,但你可能想在问题正文中澄清),并且可以/确实包含非数字的值,那么你必须非常小心使用只接受数字的函数。

With this sample data, you can see that having one value that can't be cast to a number will cause the whole query to error out.

使用此示例数据,您可以看到有一个值无法转换为数字将导致整个查询出错。

select * from diagnosis order by code;
   CODE
-----------
 555
 555.0
 555.43
 555.99
 Not a Num
(5 rows)

select code + 1 from diagnosis;
ERROR:  pg_atoi: error in "Not a Num": can't parse "Not a Num"

The usual solution to this is to either match the column value via regular expression, or use a function to test whether or now the value in each row is a number.

通常的解决方案是通过正则表达式匹配列值,或者使用函数来测试或者现在每行中的值是否为数字。

Here are two solutions, each of which depends on a function that is provided with Netezza, but not necessarily installed by default. Your administrator can install these for you.

这里有两个解决方案,每个解决方案都依赖于Netezza提供的功能,但默认情况下不一定要安装。您的管理员可以为您安装这些。

The first uses the regexp_instr from the SQL Extension Toolkit. Here you use a regular expression to match the values you want without having to do an actual CAST (implicit or explicit) to a numeric.

第一个使用SQL Extension Toolkit中的regexp_instr。在这里,您可以使用正则表达式来匹配所需的值,而无需对数字执行实际的CAST(隐式或显式)。

SELECT code FROM diagnosis
WHERE regexp_instr(code, '^555(\.\d+)?$') > 0;

  CODE
--------
 555
 555.0
 555.43
 555.99
(4 rows)

The second solution, which is a bit more involved, uses the isnumeric() UDF provided as part of the Netezza InDatabase Analytics package (in the /nz/extensions/nz/nzlua/examples directory when installed), to test whether the CODE column is a numeric before casting CODE as a numeric.

第二个解决方案,涉及更多,使用作为Netezza InDatabase Analytics包的一部分提供的isnumeric()UDF(安装时在/ nz / extensions / nz / nzlua / examples目录中),以测试CODE列是否是将CODE转换为数字之前的数字。

SELECT code
FROM (
      SELECT code
      FROM diagnosis
      WHERE isnumber(code)
   )
   foo
WHERE floor(code::NUMERIC(38,2)) = 555;

  CODE
--------
 555
 555.0
 555.43
 555.99
(4 rows)

Both of these functions are included with Netezza, but both require installation by your administrator before you can use them. In each case this is a simple task for the administrator, although they may not be aware of their availability.

这两个功能都包含在Netezza中,但两者都需要管理员安装才能使用它们。在每种情况下,这对管理员来说都是一项简单的任务,尽管他们可能不知道他们的可用性。

#1