Sql function remove numeric characters from column example

Share Me
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

today now in this post i will show you sql function for remove numeric characters from column. We know Mysql custom function is a very good and also very interesting concept. So now in this posts i want to create one custom mysql function for the remove numeric characters from table field value. Now in this function through we can also remove the numeric characters from string in mysql. So now in the following sql query fire in our mysql or mssql database and also check how it works. So, lets start and create a function and how to use in the select statement in sql query.

Create removeNumber function:

DROP FUNCTION IF EXISTS removeNumber; 
DELIMITER | 
CREATE FUNCTION removeNumber( str CHAR(32) ) RETURNS CHAR(16) 
BEGIN 
  DECLARE i_val, len SMALLINT DEFAULT 1; 
  DECLARE ret CHAR(32) DEFAULT ''; 
  DECLARE c CHAR(1); 
  SET len = CHAR_LENGTH( str ); 
  REPEAT 
    BEGIN 
      SET c = MID( str, i_val, 1 ); 
      IF c REGEXP '[[:alpha:]]' THEN 
        SET ret=CONCAT(ret,c); 
      END IF; 
      SET i_val = i_val + 1; 
    END; 
  UNTIL i_val > len END REPEAT; 
  RETURN ret; 
END | 
DELIMITER ;

Use With Select Query:

SELECT removeNumber(users.name) FROM `users`

Read Also : How to Create Custom Blade Directive in Laravel?

Thanks for read. I hope it help you. For more you can follow us on facebook.

About code chef

My name is Shahriar sagor. I'm a developer. I live in Bangladesh and I love to write tutorials and tips that will help to other Developer's. I am a big fan of PHP, Javascript, JQuery, Laravel, Codeigniter, VueJS, AngularJS and Bootstrap from the early stage.

View all posts by code chef →