Take field letters in MYSQL

Question

I have a field with information in this pattern: "PLC__Line" 34355655413.3912.
What can I use to take out the letters, _ and " in a select, leaving only the numbers and the dot?

In Postgresql I know there is the Translate function, but what can be done in MYSQL?

Edit 1: With Function below I managed to get the lyrics out, but the point is also gone, and it’s important. How can I edit this Function so that it also leaves the point?

DELIMITER $$

CREATE FUNCTION `testdb`.`GetNumber` (field varchar(100))
RETURNS VARCHAR(100)
BEGIN
DECLARE ls INTEGER;
DECLARE i INTEGER;
DECLARE str varchar(100);
SET ls  = (select length(field));
SET i   = 1;
SET str = "";
WHILE i <= ls DO            
    IF ((substring(field, i,1) REGEXP '[0-9]') <> 0) THEN
        SET str = CONCAT(str, convert(substring(field, i,1) USING UTF8));           
    END IF;
    SET i = i  + 1;
END WHILE;
RETURN str;
END;

Answer 1

Try creating the following function:

DROP FUNCTION IF EXISTS alphanum; 
DELIMITER | 
CREATE FUNCTION alphanum( str CHAR(255) ) RETURNS CHAR(255) DETERMINISTIC
BEGIN 
  DECLARE i, len SMALLINT DEFAULT 1; 
  DECLARE ret CHAR(255) DEFAULT ''; 
  DECLARE c CHAR(1); 
  SET len = CHAR_LENGTH( str ); 
  REPEAT 
    BEGIN 
      SET c = MID( str, i, 1 ); 
      IF c REGEXP '[[:alnum:]]' THEN 
        SET ret=CONCAT(ret,c); 
      END IF; 
      SET i = i + 1; 
    END; 
  UNTIL i > len END REPEAT; 
  RETURN ret; 
END | 
DELIMITER ; 

Then use it as follows:

SELECT alphanum('"PLC__Line" 34355655413.3912');

More details on the Soen: How to remove all non-alpha Numeric characters from a string in Mysql?

---

Another way, also using a function:

CREATE FUNCTION IsNumeric (val varchar(255)) RETURNS tinyint 
 RETURN val REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

 CREATE FUNCTION NumericOnly (val VARCHAR(255)) 
 RETURNS VARCHAR(255)
 BEGIN
 DECLARE idx INT DEFAULT 0;
 IF ISNULL(val) THEN RETURN NULL; END IF;

 IF LENGTH(val) = 0 THEN RETURN ""; END IF;

 SET idx = LENGTH(val);
 WHILE idx > 0 DO
 IF IsNumeric(SUBSTRING(val,idx,1)) = 0 THEN
 SET val = REPLACE(val,SUBSTRING(val,idx,1),"");
 SET idx = LENGTH(val)+1;
 END IF;
 SET idx = idx - 1;
  END WHILE;
  RETURN val;
  END;

Utilizing:

SELECT NumericOnly('"PLC__Line" 34355655413.3912');

More details on the Soen: MYSQL query will remove characters from a string

Answer 2

With the link sent by R. Santos, I was able to create my Function that worked:

DELIMITER $$

CREATE FUNCTION `meubanco`.`GetNumber` (field varchar(100))
RETURNS VARCHAR(100)
BEGIN
DECLARE ls INTEGER;
DECLARE i INTEGER;
DECLARE str varchar(100);
SET ls  = (select length(field));
SET i   = 1;
SET str = "";
WHILE i <= ls DO            
    IF ((substring(field, i,1) REGEXP '[0-9.]') <> 0) THEN
        SET str = CONCAT(str, convert(substring(field, i,1) USING UTF8));           
    END IF;
    SET i = i  + 1;
END WHILE;
RETURN str;
END;