title: Identifying unicode character in nvarchar column in SQL Server
body: <p>I have a table called <code>airports</code> in a SQL Server database, with a column declared as <code>nvarchar(255)</code>. I had to declare it as <code>nvarchar</code> otherwise SSIS failed to import the data from a <code>.csv</code> file generated by an API.</p>
  
  <p>I have approx 25k records in this table, where by from what I can tell 763 have Unicode characters in them, by running this query:</p>
  
  <pre><code>select cast(name as varchar), name 
  from airports 
  where cast(name as varchar) &lt;&gt; name
  </code></pre>
  
  <p>The first row shows the following two values returned in column 1 and 2</p>
  
  <pre><code>Harrisburg Capital City Airpor  
  Harrisburg Capital City Airport
  </code></pre>
  
  <p>The first value from column 1 has had the last t stripped off it, which I assume means there is one unicode character in the string. Please let me know if I am wrong, as I am a bit useless with unicode characters. </p>
  
  <p>My question is: how can I find the unicode characters in the column, and is there a safe / recommended way to remove them?</p>
  
  <p>I did try this to see if I could find it, but it didn't do what I thought it would do.</p>
  
  <pre><code>set nocount on
  DECLARE @nstring NVARCHAR(100)
  SET @nstring =(select name from airports where fs = 'HAR')
  DECLARE @position INT
  SET @position = 1
  
  DECLARE @CharList TABLE (Position INT,UnicodeChar NVARCHAR(1),UnicodeValue INT)
  
  WHILE @position &lt;= DATALENGTH(@nstring)
  BEGIN
      INSERT @CharList
         SELECT 
             @position as Position,
             CONVERT(nchar(1),SUBSTRING(@nstring, @position, 1)) as UnicodeChar,
             UNICODE(SUBSTRING(@nstring, @position, 1)) as UnicodeValue
      SET @position = @position + 1
  END
  
  SELECT * 
  FROM @CharList[/sql]
  ORDER BY unicodevalue
  </code></pre>
  
  <p>The output is as follows</p>
  
  <pre><code>32      NULL
  33      NULL
  34      NULL
  35      NULL
  36      NULL
  37      NULL
  38      NULL
  39      NULL
  40      NULL
  41      NULL
  42      NULL
  43      NULL
  44      NULL
  45      NULL
  46      NULL
  47      NULL
  48      NULL
  49      NULL
  50      NULL
  51      NULL
  52      NULL
  53      NULL
  54      NULL
  55      NULL
  56      NULL
  57      NULL
  58      NULL
  59      NULL
  60      NULL
  61      NULL
  62      NULL
  11      32
  19      32
  24      32
  25  A   65
  20  C   67
  12  C   67
  1   H   72
  2   a   97
  13  a   97
  17  a   97
  7   b   98
  10  g   103
  15  i   105
  5   i   105
  21  i   105
  26  i   105
  18  l   108
  29  o   111
  28  p   112
  14  p   112
  9   r   114
  3   r   114
  4   r   114
  30  r   114
  27  r   114
  6   s   115
  16  t   116
  22  t   116
  31  t   116
  8   u   117
  23  y   121
  </code></pre>