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.