Share custom functions that extract numbers from strings in Mysql, mysql strings

Share custom functions that extract numbers from strings in Mysql, mysql strings

Because a number needs to be extracted from a field in a string in the mysql DATA table, I found it online and finally found an available mysql function, which can effectively extract numbers from the string.

The numeric function in the mysql extract string is as follows:
Copy codeThe Code is as follows:
Create function GetNum (Varstring varchar (50 ))
RETURNS varchar (30)
BEGIN
DECLARE v_length int default 0;
DECLARE v_Tmp varchar (50) default '';
Set v_length = CHAR_LENGTH (Varstring );
WHILE v_length> 0 DO

IF (ASCII (mid (Varstring, v_length, 1)> 47 and ASCII (mid (Varstring, v_length, 1) <58) THEN
Set v_Tmp = concat (v_Tmp, mid (Varstring, v_length, 1 ));
End if;
SET v_length = v_length-1;
End while;
Return reverse (v_Tmp );
END;

Field Usage:
Copy codeThe Code is as follows:
Select GetNum (field) from table

Take a string as an example:
Copy codeThe Code is as follows:
Select GetNum ("dfdfd666 ")

Result: 666.

In addition, this function can extract non-coherent numbers in a string, such:
Copy codeThe Code is as follows:
Select GetNum ("dfdd111fd666 ")

Result: 111666.

How does MYSQL extract numbers from strings? That is, to filter out non-numeric characters

Mysql cannot be used for single storage. If php is added, it can be implemented!
Php can directly add data + 0 to retrieve the numbers in the string ~

How to extract numbers from strings

Yes. Use the find function to locate and find the character location, and then use the LEFT function to extract it.