trim non-numeric and numerics from varchar

Question

have the following column in my SQL

identifier
----------
z250
tr015
011s

I want to extract the numbers and non numeric characters from it, so I have an output like this:

identifier | pre  | number | post
-----------+------+--------+-----
z250       | z    | 250    | NULL
tr015      | tr   | 015    | NULL
11s        | NULL | 11     | s

Can someone point in which way I need to start looking. The column has about 700 records, and the naming conventions haven't been followed, so the could be any number of characters preceding and following the numbers.

BTW: the returned characters don't need to be NULL per se, an empty String also works. The leading zero for the numbers also doesn't matter.

Could you look at the MYSQL string functions at https://dev.mysql.com/doc/refman/5.7/en/string-functions.html? Maybe take a look at the regexp function. — rajah9, Feb 15 '17 at 14:23

score 0 · Answer 1 · answered Feb 15 '17 at 14:33

0

MySQL has a number of string functions will help.

To get you started, please see the MySQL Regex page.

Here's an example that uses :alnum: for an alphanumeric search. Substituting :digit: could be of use to you, to extract the digits in the middle.

mysql> SELECT 'justalnums' REGEXP '[[:alnum:]]+';       -> 1 
mysql> SELECT '!!' REGEXP '[[:alnum:]]+';               -> 0

answered Feb 15 '17 at 14:33

rajah9

11,645
5
44
57

Thanks for pointing me towards regexes. Can you tell me how these can help me extract the numbers? So far I have only been able to check whether an entry matches a pattern, but so far I have only been able to retrieve TRUE or FALSE – ivospijker Feb 15 '17 at 14:38
Please take a look at this question, which seems to be quite similar. http://stackoverflow.com/questions/11135464/finding-number-position-in-string – rajah9 Feb 15 '17 at 15:43

score 0 · Answer 2 · edited Apr 13 '17 at 12:42

you can create functions like this:https://dba.stackexchange.com/questions/106535/how-to-split-numbers-and-text-in-mysql

With some modifications for pre and post

 DROP function IF EXISTS `pre`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `pre`(
vStr CHAR(75)) RETURNS char(32) CHARSET latin1
BEGIN 
    DECLARE vInd, vLen SMALLINT DEFAULT 1; 
    DECLARE vReturn CHAR(32) DEFAULT ''; 
    DECLARE vCharacter CHAR(1); 
    REPEAT 
    SET vLen = CHAR_LENGTH(vStr); 
    BEGIN 
        SET vCharacter = MID(vStr,1,1); 
         IF vCharacter REGEXP '[[:digit:]|[:punct:]]' THEN 
                 SET vReturn:=NULL; 
                 RETURN vReturn;
            ELSE 
            SET vCharacter = MID(vStr,vInd,1); 
        IF vCharacter REGEXP '[[:alpha:]]' THEN 
            SET vReturn=CONCAT(vReturn,vCharacter); 

        SET vInd =vInd+1; 
        ELSE 
          SET vInd:=vLen+1;

         END IF;
            END IF; 
    END; 
    UNTIL vInd > vLen END REPEAT; 
    RETURN vReturn; 
END$$

DELIMITER ;


    DROP function IF EXISTS `post`;
    DELIMITER $$
    CREATE DEFINER=`root`@`localhost` FUNCTION `post`(
    vStr CHAR(75)) RETURNS char(32) CHARSET latin1
    BEGIN 
        DECLARE vInd,limite, vLen SMALLINT DEFAULT 1; 
        DECLARE vReturn CHAR(32) DEFAULT ''; 
        DECLARE vCharacter CHAR(1); 
       SET vInd = 1; 
       SET vLen = CHAR_LENGTH(vStr);
       SET limite=vLen;
       REPEAT 


              BEGIN 
            SET vCharacter = MID(vStr,vLen,1);  
            IF vCharacter REGEXP '[[:digit:]|[:punct:]]' THEN 
                 SET vReturn:=NULL; 
                 RETURN vReturn;
            ELSE 

              SET vCharacter = MID(vStr,vLen-vInd+1,1); 

            IF vCharacter REGEXP '[[:alpha:]]' THEN 
               SET vReturn=CONCAT(vCharacter,vReturn); 

                    SET vInd=vInd+1;  

            ELSE 
                  SET vInd=limite+1;

               END IF;


            END IF;
             END;
             UNTIL vInd > limite END REPEAT; 
            RETURN vReturn;  

    END$$

    DELIMITER ;

And query:

SELECT identifier,pre(identifier) as pre,num(identifier) as num,post(identifier) as post  FROM table

trim non-numeric and numerics from varchar

2 Answers2