SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

SQL Server: Remove Non-Alphanumeric Characters from a Column

Posted by Prashant on July 13, 2010


In database we often need to clean the non-alphanumeric characters in some column of a table. Here is the code I use to remove non-alphanumeric characters in Sql Server.

Create the table with sample data:

CREATE TABLE STRSTORE(COMMENT VARCHAR(2000))

INSERT INTO STRSTORE(COMMENT) VALUES
('1.We^lcom&<e> to Sql{Journey}.&'),
('2=http://sqljourney.wordpress.com/'),
('3#[S$tring] &Cle<@anUp'),
('4-We* are'' do+ne~')

Remove non-alphanumeric characters from the column.

WHILE @@ROWCOUNT > 1
UPDATE STRSTORE
SET COMMENT = REPLACE(COMMENT, SUBSTRING(COMMENT, PATINDEX('%[^a-zA-Z0-9 ]%', COMMENT), 1), '')
WHERE PATINDEX('%[^a-zA-Z0-9 ]%', COMMENT) <> 0

SELECT	*
FROM STRSTORE

Let me also know if you have some better ideas.

One Response to “SQL Server: Remove Non-Alphanumeric Characters from a Column”

  1. Dan said

    Thank you! Just what I was looking for! Plenty of examples of how to do this, but they all require the creation of a function.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 262 other followers

%d bloggers like this: