Sql correct filter for floats values in string column

Question

I have table invoices and there is column 'total' varchar(255). There are values like these: "500.00", "5'199.00", "129.60", "1.00" and others. I need select records and filter by total column. For example, find records where total is not more than 180.

I tried this:

SELECT total from invoices WHERE invoices.total <= '180'

But in result there are :

125.25
100.50
1593.55 - not correct
4'799.00 - not correct
1.00
-99.00
2406.52 -not correct

How can I fix it and write correct filter for this column? Thanks!

you have to convert total to number if not you make a string compare — Jens
– Jens, Commented Dec 4, 2018 at 12:23

Fahmi · Accepted Answer · 2018-12-04 12:25:28Z

1

You can use cast() function to convert it in float

SELECT total from invoices WHERE cast(invoices.total as decimal(16,2)) <= 180

answered Dec 4, 2018 at 12:25

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

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

1 Comment

Salman Arshad Over a year ago

Cast will convert 4'799.00 to 4.

Gordon Linoff · Accepted Answer · 2018-12-04 12:25:40Z

0

Why are you storing numbers as strings? That is a fundamental problem with your data model, and you should fix it.

Sometimes, we are stuck with other people's really, really, really bad decisions. If that is the case, you can attempt to solve this with explicit conversion:

SELECT i.total 
FROM invoices i
WHERE CAST(REPLACE(i.total, '''', '') as DECIMAL(20, 4)) <= 180;

Note that this will return an error if you have other unexpected characters in your totals.

answered Dec 4, 2018 at 12:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

4 Comments

Forstack Forstack Over a year ago

Thanks. String because there must be saved format for total as 1'500.00 and others, cant save it as float.

Raymond Nijland Over a year ago

@ForstackForstack storing decimals in string is just very very very plain wrong when you calculate on these strings the results can and will not be precise not something you want to have in a invoice system

Salman Arshad Over a year ago

@ForstackForstack you must learn to format the results on the front end.

Slava Over a year ago

@ForstackForstack totally agree with comments above. You have to save prices in number format in DB. If you need a specific format for showing prices or if you receive them in specific format - you can just transform them.

Pragna · Accepted Answer · 2018-12-04 13:07:20Z

If the string starts with a number, then contains non-numeric characters, you can use the CAST() function or convert it to a numeric implicitly by adding a 0:

SELECT CAST('1234abc' AS UNSIGNED); -- 1234
SELECT '1234abc'+0; -- 1234

To extract numbers out of an arbitrary string you could add a custom function like this:

DELIMITER $$

CREATE FUNCTION `ExtractNumber`(in_string VARCHAR(50)) 
RETURNS INT
NO SQL
BEGIN
    DECLARE ctrNumber VARCHAR(50);
    DECLARE finNumber VARCHAR(50) DEFAULT '';
    DECLARE sChar VARCHAR(1);
    DECLARE inti INTEGER DEFAULT 1;

    IF LENGTH(in_string) > 0 THEN
        WHILE(inti <= LENGTH(in_string)) DO
            SET sChar = SUBSTRING(in_string, inti, 1);
            SET ctrNumber = FIND_IN_SET(sChar, '0,1,2,3,4,5,6,7,8,9'); 
            IF ctrNumber > 0 THEN
                SET finNumber = CONCAT(finNumber, sChar);
            END IF;
            SET inti = inti + 1;
        END WHILE;
        RETURN CAST(finNumber AS UNSIGNED);
    ELSE
        RETURN 0;
    END IF;    
END$$

DELIMITER ;

Once the function is defined, you can use it in your query:

SELECT total from invoices WHERE ExtractNumber(invoices.total) <= 180

Collectives™ on Stack Overflow

Sql correct filter for floats values in string column

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related