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

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

Coding for fun

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Monitoring Cron Runtime In Ruby

Is Lift & Shift actually a quick and painless path to the cloud?

Model Validations For Rails

Chris Farley and David Spade in the film Tommy Boy, performing a botched sales pitch.

GIT — How to use

Playing with AMD GPU Software Stack

This is my first story on Medium.

Hello, from Klarity

Jupyter .ipynb Setup Guide for Windows 10 PC (Commentary Free)

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
LAI TOCA

LAI TOCA

Coding for fun

More from Medium

Monitor Virtual machine changes by Azure Event Grid and Logic Apps

Raml 1.0 in Mulesoft — Basics

Salesforce to Oracle DB using Mulesoft.

Overview Of MuleSoft