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
– Tim Biegeleisen, Commented Sep 12, 2016 at 7:30
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
– Aashick, Commented Sep 12, 2016 at 7:39
I think this question might help : stackoverflow.com/q/13027309/2869791 — Sarath Chandra
– Sarath Chandra, Commented Sep 12, 2016 at 9:08
GIGO -- Parse the data before putting it into the database. — Rick James
– Rick James, Commented Sep 12, 2016 at 22:38

A Biswas · Accepted Answer · 2016-09-12 07:40:34Z

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, 2016 at 7:40

A Biswas

4316 silver badges12 bronze badges

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

4 Comments

Aashick Over a year ago

Is there any possible way, with out creating any FUNCTIONS?

Drew Over a year ago

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 Over a year ago

@Aashick this is basically what regex is: a crippled turtle solving your dilemma. O(n) of regex isn't exactly a cheetah.

A Biswas Over a year ago

see function is the robust method but you can also use this :- SELECT cost FROM Costdetails WHERE cost REGEXP '^[0-9]+$';

Sarath Chandra · Accepted Answer · 2016-09-12 09:43:43Z

0

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

SELECT cast('1000/-' as UNSIGNED)

edited Sep 12, 2016 at 9:43

Sarath Chandra

1,87621 silver badges40 bronze badges

answered Sep 12, 2016 at 7:53

soniabhishek36

595 bronze badges

1 Comment

Sarath Chandra Over a year ago

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/-')

Collectives™ on Stack Overflow

MySQL How to get exact integer values from the varchar column

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related