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
Job for Mysqld.Service Failed See "Systemctl Status Mysqld.Service"
Select Multiple Columns Count One Column and Group by One Column in One Table
Pg_Restore: [Archiver] Unsupported Version (1.14) in File Header
Sql Call Stored Procedure for Each Row Without Using a Cursor
Select Column Based on Column Name Stored in Another Table
Delete Rows With Date Older Than 30 Days With SQL Server Query
Subtract Values from Two Columns in SQL Query
Mysql Error 1264: Out of Range Value for Column
Find Out Count of Employees Joined in January Month
Executing SQL Scripts on Docker Container
How to Exclude Specific Row from Query in MySQL
Loop Through Table Rows and Call Stored Procedure on Every Row
Remove Blank Line in Between Select Queries When Spooling to CSV File
Sql Count All Word Occurrences from a Table
Mysql Left Join Not Returning Null for Missing Rows
How to Change Column Order in a Table Using SQL Query in SQL Server 2005