SQL Command for removing characters like '!' or '*' from data in a MySQL column

I suspect this to be a relatively easy fix for someone with a greater grasp on SQL.

Some of the data in the 'Name' field of my db have a '!' or '*' in the name, usually at the beginning of the name. I want to remove those unnecessary characters.

Is there an SQL command that will achieve such a lofty goal with alacrity and simplicity?

Thanks in advance.

Answers


Use MySQL REPLACE function in a UPDATE query without any WHERE clause like this:

UPDATE tablename SET Name = REPLACE(Name, '!', '');
UPDATE tablename SET Name = REPLACE(Name, '*', '');

To do it in a single query, you can use this:

UPDATE tablename SET Name = REPLACE(REPLACE(Name, '*', ''), '!', '');

SELECT REPLACE(REPLACE(column,'!',''),'*','')

perhaps? See http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace


Need Your Help

Creating Service layer and DAO layer (interface+implentation) or implentation only

java oop design java-ee structure

i am confused about the structure of creating service layer and DAO layer:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.