Search code examples
sql-serverbcp

SQL Server remove special character from table

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
Solution

  • 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')