ItSolutionStuff.com

MySQL Query to Remove Special Characters from String

By Hardik Savani • February 5, 2023
PHP MySql SQL MSSQL

Mysql custom fucntion is a very prety and intresting concept. In this posts i want to create one custom mysql function for remove special characters from table field value. this function through you can also remove special 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 removeSpacialChar function:

CREATE FUNCTION `removeSpacialChar`(`in_str` varchar(4096)) RETURNS varchar(4096) CHARSET utf8

BEGIN

DECLARE out_str VARCHAR(4096) DEFAULT '';

DECLARE c VARCHAR(4096) DEFAULT '';

DECLARE pointer INT DEFAULT 1;

IF ISNULL(in_str) THEN

RETURN NULL;

ELSE

WHILE pointer <= LENGTH(in_str) DO

SET c = MID(in_str, pointer, 1);

IF (ASCII(c) >= 48 AND ASCII(c) <= 57) OR (ASCII(c) >= 65 AND ASCII(c) <= 90) OR (ASCII(c) >= 97 AND ASCII(c) <= 122) THEN

SET out_str = CONCAT(out_str, c);

ELSE

SET out_str = CONCAT(out_str, ' ');

END IF;

SET pointer = pointer + 1;

END WHILE;

END IF;

RETURN out_str;

END

Use With Select Query:

SELECT removeSpacialChar(users.name) FROM `users`

I hope it can help you...

Tags: MySql
Hardik Savani

Hardik Savani

I'm a full-stack developer, entrepreneur, and founder of ItSolutionStuff.com. Passionate about PHP, Laravel, JavaScript, and helping developers grow.

📺 Subscribe on YouTube

We Are Recommending You

Node JS CRUD with MySQL Tutorial Example

Read Now →

How to Create a REST API using Node.js and MySQL?

Read Now →

PHP MySQL Contact US Form with Validation Example

Read Now →

PHP Import Excel File into MySQL Database Tutorial

Read Now →

Laravel Where Clause with MySQL Function Example

Read Now →

How to Add MySQL Trigger from Migration in Laravel?

Read Now →

How to Import CSV File using MySQL?

Read Now →

How to fetch this week records in MySql ?

Read Now →

How to Check Empty or Null Data using MySQL Query?

Read Now →

How to Copy One Table Data into Another Table using MySQL?

Read Now →

How to count unique domains from email address field in MySQL ?

Read Now →

Which MySQL Datatype use for Store an IP Address?

Read Now →