How to find and remove special character in a string in SQL SERVER

 In this tutorial, we will see how to find and remove special characters in a string in an SQL server.

Create a Table

  

CREATE TABLE [dbo].[Employees](

       [EmployeeID] [int] IDENTITY(1,1) NOT NULL,

       [LastName] [nvarchar](20) NOT NULL,

       [FirstName] [nvarchar](10) NOT NULL,

       [Title] [nvarchar](30) NULL

       )

Insert Data in a Table

SET IDENTITY_INSERT [dbo].[Employees] ON

INSERT[dbo].[Employees] ([EmployeeID],[LastName], [FirstName],[Title]) VALUES (1, N’Davolio’, N’N^an#cy’, N’Sales Representative’)

INSERT[dbo].[Employees] ([EmployeeID],[LastName], [FirstName],[Title]) VALUES (2, N’Fuller’, N’&Andrew!’, N’Vice President, Sales’)

INSERT[dbo].[Employees] ([EmployeeID],[LastName], [FirstName],[Title]) VALUES (3, N’Leverling’, N’Ja#n$et^’, N’Sales Representative’)

INSERT[dbo].[Employees] ([EmployeeID],[LastName], [FirstName],[Title]) VALUES (4, N’Peacock’, N’Ma@rgaret’, N’Sales Representative’)

INSERT[dbo].[Employees] ([EmployeeID],[LastName], [FirstName],[Title]) VALUES (5, N’Buchanan’, N’Steven’, N’Sales Manager’)

INSERT[dbo].[Employees] ([EmployeeID],[LastName], [FirstName],[Title]) VALUES (6, N’Suyama’, N’Michael’, N’Sales Representative’)

INSERT[dbo].[Employees] ([EmployeeID],[LastName], [FirstName],[Title]) VALUES (7, N’King’, N’Robert’, N’Sales Representative’)

INSERT[dbo].[Employees] ([EmployeeID],[LastName], [FirstName],[Title]) VALUES (8, N’Callahan’, N’Laura’, N’Inside Sales Coordinator’)

INSERT[dbo].[Employees] ([EmployeeID],[LastName], [FirstName],[Title]) VALUES (9, N’Dodsworth’, N’Anne’, N’Sales Representative’)

SET IDENTITY_INSERT [dbo].[Employees] OFF

 

Why it is needed

Suppose some time what append while entering data, some garbage data is entered into the database, at that time it should be cleaning the data is very painful if the data is huge in a table.

Now let’s see

Find a Special Character in a String

SELECT Employeeid,LastName FROM Employee
WHERE LastName like '%[@,#,$,%,*]%'

Replace Special Character in SQL

Replace Function

Replace function – Using the Replace function you can remove a special character from a database field. This function only replaces a single special character.

 UPDATE Employees set FirstName = REPLACE(FirstName,'^','') where EmployeeID=EmployeeID

Remove multiple special characters 

If it contains multiple special characters in a string. In this case, we will create a function. 

CREATE function  Removespecialcharatersinstring(@string varchar(250))

returns varchar(250)

AS

BEGIN

 DECLARE @strvalue varchar(250) = '%[^0-9A-Z]%'

 WHILE PATINDEX(@strvalue,@string)>0

                 SET @string = Stuff(@string,PATINDEX(@strvalue,@string),1,'')

                 Return @string

 END

  SELECT[dbo].[removespecialcharatersinstring]('N^an#cy123&2')