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:
1: CREATE TABLE STRSTORE(COMMENT VARCHAR(2000))
2:
3:
4: INSERT INTO STRSTORE(COMMENT) VALUES
5: ('1.We^lcom&<e> to Sql{Journey}.&'),
6: ('2=http://sqljourney.wordpress.com/'),
7: ('3#[S$tring] &Cle<@anUp'),
8: ('4-We* are'' do+ne~')
Remove non-alphanumeric characters from the column.
1: WHILE @@ROWCOUNT > 1
2: UPDATE STRSTORE
3: SET COMMENT = REPLACE(COMMENT, SUBSTRING(COMMENT, PATINDEX('%[^a-zA-Z0-9 ]%', COMMENT), 1), '')
4: WHERE PATINDEX('%[^a-zA-Z0-9 ]%', COMMENT) <> 0
5:
6:
7: SELECT * FROM STRSTORE
Let me also know if you have some better ideas.
