MySQL Query to Remove Numbers from String in a Column Example
Mysql custom fucntion is a very prety and intresting concept. In this posts i want to create one custom mysql function for remove numeric characters from table field value. this function through you can also remove numeric characters from string in mysql. in following sql query fire in your mysql or mssql database and check how it works. So, lets create function and how to use in select statement in sql query.
Create removeNumber function:
DROP FUNCTION IF EXISTS removeNumber;
CREATE FUNCTION removeNumber( str CHAR(32) ) RETURNS CHAR(16)
DECLARE i_val, len SMALLINT DEFAULT 1;
DECLARE ret CHAR(32) DEFAULT '';
DECLARE c CHAR(1);
SET len = CHAR_LENGTH( str );
SET c = MID( str, i_val, 1 );
IF c REGEXP '[[:alpha:]]' THEN
SET i_val = i_val + 1;
UNTIL i_val > len END REPEAT;
Use With Select Query:
SELECT removeNumber(users.name) FROM `users`
I hope it can help you...
We are Recommending you
- Node JS CRUD with MySQL Tutorial Example
- How to Create a REST API using Node.js and MySQL?
- How to Export Mysql Database using Command Line in Ubuntu?
- How to Import Database in Mysql using Command Line in Ubuntu?
- MySQL Query to Remove Special Characters from String
- Laravel Where Clause with MySQL Function Example
- How to Add MySQL Trigger from Migration in Laravel?
- How to Import CSV File using MySQL?
- How to fetch this week records in MySql ?
- How to Check Empty or Null Data using MySQL Query?
- How to Copy One Table Data into Another Table using MySQL?
- How to count unique domains from email address field in MySQL ?
- Which MySQL Datatype use for Store an IP Address?
- Mysql Hexadecimal Color Code Store in Binary Datatype Example