SQL Server remove special character from table

Question

I have table in SQL Server with a column Team, but when inserting data through BCP, some special character are on the first line:

 TEAM   
 ∩╗┐1.Insta Acq

I tried with the code shown here, but with no success. I copied the special symbol and paste it in the replace function as

 REPLACE(COLUMN NAME,'∩╗┐1.Insta Acq', '1.Insta Acq' )

Datatypes:

 COLUMN_NAME    DATA_TYPE   TYPE_NAME
 -------------------------------------
 TEAM           -9          nvarchar
 SOURCE         12          varchar
 STAGE          12          varchar
 TARGET OCT'22  4           int

Sample data in the table:

 TEAM           SOURCE   STAGE                      TARGET OCT'22
------------------------------------------------------------------
 ∩╗┐1.Insta Acq Website  TB Active / TB Inactive    9000
 1.Insta Acq    Website  No Offer                   3500

Since it's an `NVARCHAR` column - you **must use** the `N` prefix in the `REPLACE` call: `REPLACE(COLUMN NAME, N'∩╗┐1.Insta Acq', N'1.Insta Acq')` to make it clear these are **Unicode** string literals — marc_s, Oct 23 '22 at 09:25

score 1 · Accepted Answer · answered Oct 23 '22 at 09:54

Since you're dealing with "special" characters that are probably Unicode, you must use the N prefix in your REPLACE call to indicate that these are Unicode string literals you're working with.

Try this:

REPLACE(COLUMN NAME, N'∩╗┐1.Insta Acq', N'1.Insta Acq')

SQL Server remove special character from table

1 Answers1