MySQL How to get exact integer values from the varchar column

Question

I have a table named as 'Costdetails'.

There is a column named as 'cost', it is a VARCHAR column. it can be anything, as given below.

Cost ssss
20000 - $
Rs - 1000/-
10000 Rupees etc.

I want to take out exact amount (Ex: 1000, 20000) From this varchar column. Tried on google about this and i got a query, and then i tried this query.

SELECT cost
FROM Costdetails
WHERE (cost REGEXP '^[0-9]' or cost REGEXP '[0-9]^' or cost REGEXP '[0-9]');


Output :

Rs-1000/-
10000 - $
$ 10000

This query helps me to fetch the rows which is having integer values. But want to remove the extra stuff from the column (Output: Like 1000, Not like Rs-1000/-).

Any idea, Thanks in advance!!!.

I don't think MySQL has the regex power to handle this out of the box. Could you use a program such as Notepad++ first, then import the data into MySQL? — Tim Biegeleisen, Sep 12 '16 at 07:30
http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql — jarlh, Sep 12 '16 at 07:31
I want to make script for this. it should fetch the column and change as per requirement, and put the data in other tables. — Aashick, Sep 12 '16 at 07:39
I think this question might help : http://stackoverflow.com/q/13027309/2869791 — Sarath Chandra, Sep 12 '16 at 09:08
GIGO -- Parse the data _before_ putting it into the database. — Rick James, Sep 12 '16 at 22:38

score 0 · Answer 1 · answered Sep 12 '16 at 07:40

0

You can use a function to complete your query . For example :-

 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(cost) from Costdetails;

answered Sep 12 '16 at 07:40

A Biswas

421
6
12

Is there any possible way, with out creating any FUNCTIONS? – Aashick Sep 12 '16 at 07:42
it's awesome when you show `select digits('junk in here 3000')` in different edge conditions at the bottom of your answer to sorta prove it – Drew Sep 12 '16 at 07:43
@Aashick this is basically what regex is: a crippled turtle solving your dilemma. O(n) of regex isn't exactly a cheetah. – Drew Sep 12 '16 at 07:44
see function is the robust method but you can also use this :- `SELECT cost FROM Costdetails WHERE cost REGEXP '^[0-9]+$';` – A Biswas Sep 12 '16 at 07:46

score 0 · Answer 2 · edited Sep 12 '16 at 09:43

0

You can use the cast function for this, as follows :-

SELECT cast('1000/-' as UNSIGNED)

edited Sep 12 '16 at 09:43

Sarath Chandra

1,850
19
40

answered Sep 12 '16 at 07:53

soniabhishek36

59
5

This approach works only if the value starts with a number. For example, works for `CAST('100/-')`. But doesn't work for `CAST('Rs. 1000/-')` – Sarath Chandra Sep 12 '16 at 09:25

MySQL How to get exact integer values from the varchar column

2 Answers2