PostgreSQL:在一个单一的SQL语法顺序的数值计算从一个文本列

Question

列有字符串值，如"1/200"，"3.5"或"6"。如何将此字符串转换为单个SQL查询中的数值?

我的实际SQL更复杂，这里有一个简单的例子:

SELECT number_value_in_string FROM table 

number_value_in_string的格式将是:

• # #
• #。# #
• #/# # #

我需要按这一列的数值排序。但当然，postgres不同意我认为1/200是一个合适的数字。

Answer 1

看到你的名字，我不得不把你的答案简化一下:

SELECT id, number_value_in_string FROM table
 ORDER BY CASE WHEN substr(number_value_in_string,1,2) = '1/'
        THEN 1/substr(number_value_in_string,3)::numeric 
        ELSE number_value_in_string::numeric END, id;

_{忽略可能的除零}

Answer 2

我将定义一个存储函数来将字符串转换为数值，大致如下:

CREATE OR REPLACE FUNCTION fraction_to_number(s CHARACTER VARYING)
RETURN DOUBLE PRECISION AS
BEGIN
   RETURN
   CASE WHEN s LIKE '%/%' THEN
       CAST(split_part(s, '/', 1) AS double_precision) 
       / CAST(split_part(s, '/', 2) AS double_precision)
   ELSE
       CAST(s AS DOUBLE PRECISION)
   END CASE
END

那么你可以ORDER BY fraction_to_number(weird_column)

如果可能的话，我会重新考虑数据设计。这么复杂真的有必要吗?

Answer 3

这个postgres SQL实现了这个技巧:

select (parts[1] :: decimal) / (parts[2] :: decimal) as quotient
FROM (select regexp_split_to_array(number_value_in_string, '/') as parts from table) x

下面是对这段代码的测试:

select (parts[1] :: decimal) / (parts[2] :: decimal) as quotient
FROM (select regexp_split_to_array('1/200', '/') as parts) x

输出:

0.005

请注意，如果列不包含正斜杠

，则需要将其包装在case语句中，以防止除零错误和/或数组越界问题等。还请注意，您可以在没有内部选择的情况下执行，但是您必须使用两次 regexp_split_to_array (每个部分一次)，并且可能会导致性能下降。然而，内联编码可能更容易，并且只接受较小的性能损失。

Answer 4

我设法解决了我的问题。谢谢所有。在一个SQL语句中，它是这样的。(我正在使用POSTGRESQL)

它将把输入的字符串排序为"#"或"#"。#"或"1/#"

SELECT id, number_value_in_string FROM table ORDER BY CASE WHEN position('1/' in number_value_in_string) = 1 
    THEN 1/substring(number_value_in_string from (position('1/' in number_value_in_string) + 2) )::numeric 
    ELSE number_value_in_string::numeric 
END ASC, id

希望这将帮助别人在那里的未来。