Handle invisible letter(s) from excel to your SQL table

MSSQL as Example

Photo from: https://www.cleanpng.com/png-microsoft-azure-sql-database-microsoft-sql-server-2463035/

Scenario

You might happen to encounter the below circumstance.

A) You got the source from excel, comparing with your data on the SQL table: Table_T that you would like to modify your table’s content to match the excel source.

Source from excel (Copy Here)

B) The simple way is to open edit windows under SSMS (SQL Server Management Studio) and COPY (crtl+c) and PASTE into the target cell (crtl+v).

Edit windows from SSMS (Paste Here)

C) Seems that everything works perfect, then we try to search the data what we have modified before to verify the result using below T-SQL:

------------------------------------------------------------------
-- T-SQL for query information according CustomNO
-------------------------------------------------------------------
SELECT
   Column_B
   CustomNO
FROM Table_T
WHERE dbo.CustomNO = '1010160297'

Woops, nothing return? Why and What happened?

By through into the data itself, we found that if we select the whole excel cell and COPY the content we desired will carry more chars we do not expect for instance as below:

Break line from the tail

So if we modified our query script:

------------------------------------------------------------------
-- T-SQL for query information according CustomNO
-------------------------------------------------------------------
SELECT
   Column_B
   CustomNO
FROM Table_T
WHERE dbo.CustomNO = '1010160297' + CHAR(13) + CHAR(10)

Then you could got what you expected.

Result sets

Next, we would love to discuss what would be better way to handle those issue?

If you are as executer role for ALTER table data from excel, write update T-SQL instead of pasting to SSMS edit windows:

UPDATE dbo.Table_T    
SET dbo.Table_T.CustomNO = '1010160297'
WHERE Column_B = 'YOUR CONDITIONAL'

Using script might avoid person that insert the unexpected chars (invisible char) into table. Plain text would enclose between ‘’.

And if you having present data existed dummy tail of line break, you could create new column for storing corrected content.

ALTER TABLE dbo.Table_T ADD CistomNO2 AS REPLACE(LTRIM(RTRIM([CustomNO])), CHAR(13)+CHAR(10), '')
-- then you could select with new column
SELECT
   Column_B
   CustomNO
FROM Table_T
WHERE CustomNO2 = '1010160297'

If you have permission problem to create database schema or only with execute permission, you might able to achieve via this way.

DECLARE @CustomNO NVARCHAR(100) = '1010160297'
SELECT
   Column_B
   CustomNO
FROM dbo.Table_T
WHERE REPLACE(LTRIM(RTRIM(CustomNO)), CHAR(13)+CHAR(10), '')=@CustomNO

Reference

• https://stackoverflow.com/questions/951518/replace-a-newline-in-tsql