16

I have a table with two columns: price (int) and price_display (varchar).

price is the actual numerical price, e.g. "9990"

price_display is the visual representation, e.g. "$9.99" or "9.99Fr"

I've been able to confirm the two columns match via regexp:

price_display not regexp format(price/1000, 2)

But in the case of a mismatch, I want to extract the value from the price_display column and set it into the price column, all within the context of an update statement. I've not been able to figure out how.

Thanks.

Improve this question

6 Answers 6

Reset to default
37

This function does the job of only returning the digits 0-9 from the string, which does the job nicely to solve your issue, regardless of what prefixes or postfixes you have.

http://www.artfulsoftware.com/infotree/queries.php?&bw=1280#815

Copied here for reference:

SET GLOBAL log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS digits;
DELIMITER |
CREATE FUNCTION digits( str CHAR(32) ) RETURNS CHAR(32)
BEGIN
  DECLARE i, len SMALLINT DEFAULT 1;
  DECLARE ret CHAR(32) DEFAULT '';
  DECLARE c CHAR(1);

  IF str IS NULL
  THEN 
    RETURN "";
  END IF;

  SET len = CHAR_LENGTH( str );
  REPEAT
    BEGIN
      SET c = MID( str, i, 1 );
      IF c BETWEEN '0' AND '9' THEN 
        SET ret=CONCAT(ret,c);
      END IF;
      SET i = i + 1;
    END;
  UNTIL i > len END REPEAT;
  RETURN ret;
END |
DELIMITER ;

SELECT digits('$10.00Fr'); 
#returns 1000
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

In addition to above solution, in case the number just starts or just ends with a letter or character (eg. $10.00 or 10.00Fr) there is a simpler more efficient solution to extract the numbers.
Awesome, thanks. A small tip to other readers: if you turn IF c BETWEEN into IF c NOT BETWEEN, you can use this function to match any characters that are not digits.
Arguably, depending on your use case, the fallback return value for the str IS NULL case should be 0 and not an empty string, since we want to extract digits.
5

One approach would be to use REPLACE() function:

UPDATE my_table
SET    price = replace(replace(replace(price_display,'Fr',''),'$',''),'.','')
WHERE  price_display not regexp format(price/1000, 2);

This works for the examples data you gave:

'$9.99'
'9.99Fr'

Both result in 999 in my test. With an update like this, it's important to be sure to back up the database first, and be cognizant of the formats of the items. You can see all the "baddies" by doing this query:

SELECT   DISTINCT price_display
FROM     my_table
WHERE    price_display not regexp format(price/1000, 2)
ORDER BY price_display;
Improve this answer

2 Comments

Thanks, this is good stuff, but I of course have a lot more currency symbols than just "$" and "Fr" so that replace chain would be out of control.
I'd be interested to see some representative results from the last query I posted. How many distinct items are there? Perhaps with a few more examples I or someone else can make a more informed piece of code. One thing I thought of is if you have items formatted like "$10" representing $10.00 then my method fails. But then you did not post that as a possibility. The last query I posted would be helpful in nailing down the query that will do the magic.
5

For me CASTING the field did the trick:

CAST( price AS UNSIGNED ) // For positive integer

CAST( price AS SIGNED ) // For negative and positive integer

IF(CAST(price AS UNSIGNED)=0,REVERSE(CAST(REVERSE(price) AS UNSIGNED)),CAST(price AS UNSIGNED)) // Fix when price starts with something else then a digit

For more details see:

https://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

Improve this answer

5 Comments

love it. Just ran a reverse on the string, casted it, and the reversed it again to get the number at the end of the string. Simple.. Effective.. Reliable. thank you.
Awesome. This is great for splitting house numbers and characters, eg. 40A into 40 and A like this: SELECT CAST(t.col AS UNSIGNED) AS "Number", REPLACE(t.col, CAST(t.col AS UNSIGNED), "") AS "Character"
I don't know what you guys are talking about. This approach is flawed: running REVERSE(CAST(REVERSE(numstring) as UNSIGNED)) on a value like string-10 will return 1 and not 10.
@WoodrowShigeru hey man am having the same problem have you find any solution
Yes, Vladiat0r's solution worked for me.
1

This is a "coding horror", relational database schemas should NOT be written like this!

Your having to write complex and unnecessary code to validate the data.

Try something like this:

SELECT CONCAT('$',(price/1000)) AS Price FROM ...

In addition, you can use a float, double or real instead of a integer.

If you need to store currency data, you might consider adding a currency field or use the systems locale functions to display it in the correct format.

Improve this answer

1 Comment

Thank you for the response. Unfortunately I have no control over the schema; I simply inherited it and I must now correct the contained data.
1

I create a procedure that detect the first number in a string and return this, if not return 0.

    DROP FUNCTION IF EXISTS extractNumber;
    DELIMITER //
    CREATE FUNCTION extractNumber (string1 VARCHAR(255)) RETURNS INT(11) 
        BEGIN
        DECLARE position, result, longitude INT(11) DEFAULT 0;
        DECLARE string2 VARCHAR(255);
        SET longitude = LENGTH(string1);
        SET result = CONVERT(string1, SIGNED);
        IF result = 0 THEN
            IF string1 REGEXP('[0-9]') THEN
                SET position = 2;
                checkString:WHILE position <= longitude DO
                    SET string2 = SUBSTR(string1 FROM position);
                    IF CONVERT(string2, SIGNED) != 0 THEN
                        SET result = CONVERT(string2, SIGNED);
                        LEAVE checkString;
                    END IF;
                    SET position = position + 1;
                END WHILE;
           END IF;
        END IF;
        RETURN result;
    END //
    DELIMITER ;
Improve this answer

Comments

1

Return last number from the string:

CREATE FUNCTION getLastNumber(str VARCHAR(255)) RETURNS INT(11)
DELIMETER //
BEGIN
    DECLARE last_number, str_length, position INT(11) DEFAULT 0;
    DECLARE temp_char VARCHAR(1);
    DECLARE temp_char_before VARCHAR(1);


IF str IS NULL THEN
    RETURN -1;
END IF;

SET str_length = LENGTH(str);

WHILE position <= str_length DO
    SET temp_char = MID(str, position, 1);

    IF position > 0 THEN
        SET temp_char_before = MID(str, position - 1, 1);
    END IF;

    IF temp_char BETWEEN '0' AND '9' THEN
        SET last_number = last_number * 10 + temp_char;
    END IF;
    IF (temp_char_before NOT BETWEEN '0' AND '9') AND 
           (temp_char BETWEEN '0' AND '9') THEN                 
        SET last_number = temp_char;
    END IF;

    SET position = position + 1;
END WHILE;

RETURN last_number;
END//
DELIMETER;

Then call this functions:

select getLastNumber("ssss111www222w"); print 222

select getLastNumber("ssss111www222www3332"); print 3332

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.