A column has a string values like "1/200", 开发者_运维问答"3.5" or "6". How can I convert this String to numeric value in single SQL query?
My actual SQL is more complicated, here is a simple example:
SELECT number_value_in_string FROM table
number_value_in_string's format will be one of:
- ##
- #.##
- #/###
I need to sort by the numeric value of this column. But of course postgres doesn't agree with me that 1/200 is a proper number.
Seeing your name I cannot but post a simplification of your answer:
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;
Ignoring possible divide by zero.
I would define a stored function to convert the string to a numeric value, more or less like this:
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
Then you can ORDER BY fraction_to_number(weird_column)
If possible, I would revisit the data design. Is it all this complexity really necessary?
This postgres SQL does the trick:
select (parts[1] :: decimal) / (parts[2] :: decimal) as quotient
FROM (select regexp_split_to_array(number_value_in_string, '/') as parts from table) x
Here's a test of this code:
select (parts[1] :: decimal) / (parts[2] :: decimal) as quotient
FROM (select regexp_split_to_array('1/200', '/') as parts) x
Output:
0.005
Note that you would need to wrap this in a case
statement to protect against divide-by-zero errors and/or array out of bounds issues etc if the column did not contain a forward slash
Note also that you could do it without the inner select, but you would have to use regexp_split_to_array
twice (once for each part) and you would probably incur a performance hit. Nevertheless, it may be easier to code in-line and just accept the small performance loss.
I managed to solve my problem. Thanks all. It goes something like this, in a single SQL. (I'm using POSTGRESQL)
It will sort a string coming in as either "#", "#.#" or "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
Hope this will help someone outhere in the future.
精彩评论