Background

I was using SQL Server Management Studio ( SSMS ) and experienced an unusual error.

Let us cover it succinctly.

Query

select a =  1

union

select b =  2

union all

select c =  3

union all

select d =  4

union all

select e =  5 

 

Error Message

Image

Textual

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near ' '.

 

Troubleshooting

Online Tools

Outline

Here are some online tools for detecting hidden characters

1. soscisurvey.de
   • View non-printable Unicode characters
     Link

Tools

soscisurvey.de

View non-printable Unicode characters

Image

 

Explanation

1. Hard Space
   • The hard space is represented as U+A0
   • Here is the image representation
     
   • Character Found
     • 01
       • 
     • 02
       • 

 

 

Desktop Tools

Outline

Here are some desktop tools for managing hidden characters

1. notepad++

Tools

notepad++

Search specific character – Count

Steps

1. Launch Notepad++
2. Load File
3. Access Find Dialog
   • In Find Dialog
     • Find What:-
       • In the find what text box please enter “\xa0“
     • Search Modes:-
       • Normal
         • ( Matches will not be found )
       • Extended
         • ( Matches are found )
       • Regular Expression
         • ( Matches are found )
     • Count Button
       • Access the Count Button to initiate the counting

Image

Explanation

1. We counted 5 matches

Search specific character – Find/Next

Steps

1. Launch Notepad++
2. Load File
3. Access Find Dialog
   • In Find Dialog
     • Find What:-
       • In the find what text box please enter “\xa0“
     • Search Modes:-
       • Normal
         • ( Matches will not be found )
       • Extended
         • ( Matches are found )
       • Regular Expression
         • ( Matches are found )
     • “Find Next” Button
       • Access the “Find Next ” Button to initiate the counting

Image

Explanation

1. We were able to find the instances where the special character was in use

 

Search specific character – Replace

Steps

1. Launch Notepad++
2. Load File
3. Access Find Dialog
   • In the Find Dialog, please access the “Replace” tab
     • Find What:-
       • In the find what text box please enter “\xa0“
     • Replace What:-
       • In the replace with text box, please enter the a single space bar or multiple space bars
     • Search Modes:-
       • Normal
         • ( Matches will not be found )
       • Extended
         • ( Matches are found )
       • Regular Expression
         • ( Matches are found )
     • “Replace All” Button
       • Access the “Replace All” Button to replace all “hard spaces“

Image – Find Replace – Initial

Image – Find Replace – Post Changes

Explanation

1. We were able to replace all the hard spaces

 

Test Environment

This is a constructed test.

Here are my pre-requisites.

1. SQL Server Version
   • Reproducible
     • SQL Server v2005
     • SQL Server v2008 R2
   • Remediated
     • • Microsoft SQL Server 2012 (SP1)
2. File Encoding
   • UTF-8

 

Source Code

GitHub

Gist

GitLab

 

Referenced Work

1. La De Du
   • How to Enter a No-Break Space on Windows
     Link
2.  soscisurvey.de
   • Tools
     • View Characters
       Link
3. FileFormatInfo
   • Unicode Character ‘NO-BREAK SPACE’ (U+00A0)
     Link
4.  Notepad++
   • GitHub
     • “non-breaking spaces” are invisible even with “Show All Characters” #5783
       Link
   • community.notepad-plus-plus
     • Show No Break Space
       Link
     • How to change all invalid characters to spaces
       Link
     • Invisible Spaces
       Link
5. Scintilla
   • #974 Display of “special” whitespaces
     Link
6. David Klempfner
   • The Zero Width Space
     Link
7. IT Support Guides
   • Classic Editor / TinyMCE – How to automatically insert Non-Breaking Space using Ctrl + Shift + Space
     Link