PostgreSQL: IN A SINGLE SQL SYNTAX order by numeric value computed from a text column

Question

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.

I know of no database that would recognize a fraction as a number. They do things in decimals. If you try to convert to a decimal beware of integer math in many dbs 1/200 = 0. — HLGEM
– HLGEM, Commented Sep 30, 2011 at 23:45
Why don't you just store it as a number to begin with? Or at least a pair of numbers, i.e. numerator and denominator? Otherwise you can use a regex to split it and do math on the pieces. Seems overly clever and fragile to me. — Scott Marlowe
– Scott Marlowe, Commented Oct 1, 2011 at 0:02
@ScottMarlowe: i'm working on a already established code base so i can't do that. i wish i could though. my life would be much easier — RoundOutTooSoon
– RoundOutTooSoon, Commented Oct 1, 2011 at 0:18

Erwin Brandstetter · Accepted Answer · 2011-10-01 02:05:45Z

1

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.}

answered Oct 1, 2011 at 2:05

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RoundOutTooSoon Over a year ago

nice one!! thanks for bringing down my name's reputation +1 for that :) btw, there is no such thing as divide by zero error. jk

kevin cline · Accepted Answer · 2011-10-01 00:13:04Z

1

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?

edited Oct 1, 2011 at 0:13

answered Oct 1, 2011 at 0:04

kevin cline

2,8063 gold badges28 silver badges40 bronze badges

4 Comments

RoundOutTooSoon Over a year ago

the thing is i'm working with this huge code base can I can't modify the database. Only one sql is allowed to pass to the database in the function i'm modifying. But thanks though, the CASE WHEN in-line SQL is definitely legit!

Scott Marlowe Over a year ago

I'd alter the table to have a custom type, and store this as both parts properly. I'm so sorry you have to work with this. OTOH if this will never ever change you can do it this way, but I'd at least add some error correction to prevent 200/0 etc from throwing an error or being stored or whatever. Or can you not add constraints on the db?

RoundOutTooSoon Over a year ago

@ScottMarlowe: I think I will need to talk to the dev team soon about this. But for now I don't think that will change soon. They will have their reason because the 'custom' field should be string type so it can take almost anything given to it, then process the value accordingly depending on each case :(

Scott Marlowe Over a year ago

So are they gonna store 10*4/13^8 or something in it eventually? That's a pretty crappy requirement.

Bohemian · Accepted Answer · 2011-10-01 00:27:16Z

1

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.

edited Oct 1, 2011 at 0:27

answered Oct 1, 2011 at 0:22

Bohemian♦

427k103 gold badges604 silver badges750 bronze badges

1 Comment

RoundOutTooSoon Over a year ago

awesome idea! +1 for the regexp_split_to_array()

RoundOutTooSoon · Accepted Answer · 2011-10-03 17:43:26Z

1

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.

edited Oct 3, 2011 at 17:43

answered Oct 1, 2011 at 0:22

RoundOutTooSoon

9,9018 gold badges38 silver badges52 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: IN A SINGLE SQL SYNTAX order by numeric value computed from a text column

4 Answers 4

1 Comment

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related