How to Get Only Digits from String in MySQL

How to get only Digits from String in mysql?

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 ExtractNumber("abc1234def") AS number; -- 1234

How do you extract a numerical value from a string in a MySQL query?

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

How to Get only number from string mysql

You can use the REGEXP_SUBSTR function for this.

SELECT IF(REGEXP_SUBSTR('store(2)','[0-9]+') != '', REGEXP_SUBSTR('store(2)','[0-9]+'), 1);

Selecting first 4 numbers from a string MySQL

Using locate(), least(), and substr()

SQLFiddle Demo

select col,SUBSTR(col,LEAST(
if (Locate(0,col) >0,Locate(0,col),999),
if (Locate(1,col) >0,Locate(1,col),999),
if (Locate(2,col) >0,Locate(2,col),999),
if (Locate(3,col) >0,Locate(3,col),999),
if (Locate(4,col) >0,Locate(4,col),999),
if (Locate(5,col) >0,Locate(5,col),999),
if (Locate(6,col) >0,Locate(6,col),999),
if (Locate(7,col) >0,Locate(7,col),999),
if (Locate(8,col) >0,Locate(8,col),999),
if (Locate(9,col) >0,Locate(9,col),999)
),4) as result from test;

Test Results:

mysql> create table test ( col varchar(15));
Query OK, 0 rows affected (0.70 sec)

mysql> insert into test (col) values
-> ('CC123484556'),
-> ('CC492014512'),
-> ('BUXT122256690');
Query OK, 3 rows affected (0.13 sec)
Records: 3 Duplicates: 0 Warnings: 0

Output:

mysql> select * from test;
+---------------+
| col |
+---------------+
| CC123484556 |
| CC492014512 |
| BUXT122256690 |
+---------------+
3 rows in set (0.00 sec)

mysql> select col,SUBSTR(col,LEAST(
-> if (Locate(0,col) >0,Locate(0,col),999),
-> if (Locate(1,col) >0,Locate(1,col),999),
-> if (Locate(2,col) >0,Locate(2,col),999),
-> if (Locate(3,col) >0,Locate(3,col),999),
-> if (Locate(4,col) >0,Locate(4,col),999),
-> if (Locate(5,col) >0,Locate(5,col),999),
-> if (Locate(6,col) >0,Locate(6,col),999),
-> if (Locate(7,col) >0,Locate(7,col),999),
-> if (Locate(8,col) >0,Locate(8,col),999),
-> if (Locate(9,col) >0,Locate(9,col),999)
-> ),4) as result from test;
+---------------+--------+
| col | result |
+---------------+--------+
| CC123484556 | 1234 |
| CC492014512 | 4920 |
| BUXT122256690 | 1222 |
+---------------+--------+
3 rows in set (0.00 sec)

Mysql select only numbers from substring result

SUBSTRING_INDEX might work here:

SELECT
post_content,
SUBSTRING_INDEX(SUBSTRING_INDEX(post_content, '[table id=', -1), ' ', 1) AS id
FROM wp276_posts
WHERE
post_content LIKE '%[table%' AND
post_status = 'publish' -- you seem to want equivalence here
ORDER BY
ID;

The inner call to SUBSTRING_INDEX above returns everything to the right of [table id=. The outer call then takes what is to the left of the space which is presumed to follow the id number.

how to get only number from a varchar from a mysql table

If that's the only input and output you're expecting, it might just be best to use replace:

SELECT replace(replace(replace('€ 899,00', '€', ''), ',', '.'), ' ', '') as digits

Output of the above select is 899.00

The '€ 899,00' could be a column of your table that you're selecting from i.e.:

SELECT replace(replace(replace(mycolumn, '€', ''), ',', '.'), ' ', '') as digits 
FROM mytable


Related Topics



Leave a reply



Submit