Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Server - How to Remove Special Characters from String in SQL Server 2008

Jun 2, 2014
Introduction:

Here I will explain how to remove special characters from string in
sql server 2008 with spaces or sql server replace special characters in string with spaces in sql server 2008 or replace unwanted characters in string in sql server.
Description:
In previous articles I explained replace multiple spaces with single space in sql, reseed identity column value in sql server, get time difference between two dates in sql, substring function in SQL server and many articles relating to SQL server. Now I will explain how to remove special characters from string with spaces in sql server 2008.

To replace special characters from string with spaces in sql server we need to write the query like this


DECLARE @regex INT,@string varchar(100)
SET @string='welcome-to''aspdotnet=sures@h.com#$'
SET @regex = PATINDEX('%[^a-zA-Z0-9 ]%', @string)
WHILE @regex > 0
BEGIN
SET @string = STUFF(@string, @regex, 1, ' ' )
SET @regex = PATINDEX('%[^a-zA-Z0-9 ]%', @string)
END
SELECT @string
Once we run above query we will get output like as shown below

Output:




If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

4 comments :

Anonymous said...

Sir, please explain the what's work PATINDEX and STUFF.

Anonymous said...

Thanks for your article. it is very much helpful to me.

Anonymous said...

please explainPATINDEX and STUFF.

Anonymous said...

please tell me hoe to remove space

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.