44

Where can I see all the non-visible characters stored on a varchar or char field in SQL Server? e.g. \n (new-line), \r (carriage return) and other "non-printable" characters?

How to print them (as they are not printable but perform some action on the text)?

Vitox
  • 3,852
  • 29
  • 30
Harold Sota
  • 7,490
  • 12
  • 58
  • 84
  • I export my query result set in excel, andthere are some row break in srepreadsheet, there is some special character like new line that cause these. – Harold Sota Aug 12 '11 at 09:46
  • newline is not a particularly special character... – Mitch Wheat Aug 12 '11 at 09:48
  • @Mitch whatever..., there is any way? – Harold Sota Aug 12 '11 at 10:03
  • 3
    @Haroldis, Mitch's point is that "special characters" means nothing: it is not a precise technical term and your definition of "special" may be different from mine. I'm guessing your requirement is to find characters that are "not letters" but what is a letter for you? You need to consider accented characters, non-European scripts, punctuation, whitespace etc. Please provide a more precise definition of what you mean and some examples. – Pondlife Aug 12 '11 at 10:19
  • 2
    If by "special" you mean the "control" characters, e.g. ASCII 0-31 and 127, then I think all of them. If you mean how to find whether your records include such control characters or how to print them (as they are usually not printable but perform some action when one tries to print them), that's a different question. – ypercubeᵀᴹ Aug 12 '11 at 11:16
  • Re. "closed as 'unclear'": O-mi-GOSH! SO sorry @HaroldSOTA you have to deal with so many Cmdr. Data's and Sheldon's on this Site! IMHO, it *should*'ve been obvious common sense (even with your original unedited Q) to the vast majority of reasonable *human* developers what you're asking, and KM's "Aug 12 '11 at 13:06" answer should've *immediately* been given! Just answer the *freakin' Q! Stop trying to "compile" it as if you were a "'human'" computer! – Tom Aug 07 '18 at 22:44
  • 1
    @Tom, thank you for you work, your common sense will make stackoverflow a better place – Harold Sota Aug 08 '18 at 18:37

4 Answers4

43

You probably just need to see the ASCII and EXTENDED ASCII character sets. As far as I know any of these are allowed in a char/varchar field.

If you use nchar/nvarchar then it's pretty much any character in any unicode set in the world.

enter image description here

enter image description here

JNK
  • 63,321
  • 15
  • 122
  • 138
  • which datatype is recommented to save copy right information ? for eg. "© my company 2014" – Hitesh Sep 02 '14 at 05:24
  • Commenting on a too old thread, but I am unable to store this data - "Bănărescu" in a nvarchar/varchar/text field. Any idea? – Nitesh Jul 13 '15 at 14:21
  • @Nitesh need more information - what error are you getting? – JNK Jul 13 '15 at 14:24
  • I am updating a column using UPDATE statement, it updates successfully. however the updated value is "banarescu" instead of "Bănărescu". To give further insight, the collation of DB is - Latin1_General_CI_AI – Nitesh Jul 13 '15 at 14:26
22

EDIT based on comments:

If you have line breaks in your result set and want to remove them, make your query this way:

SELECT
    REPLACE(REPLACE(YourColumn1,CHAR(13),' '),CHAR(10),' ')
   ,REPLACE(REPLACE(YourColumn2,CHAR(13),' '),CHAR(10),' ')
   ,REPLACE(REPLACE(YourColumn3,CHAR(13),' '),CHAR(10),' ')
   --^^^^^^^^^^^^^^^           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   --only add the above code to strings that are having line breaks, not to numbers or dates
   FROM YourTable...
   WHERE ...

This will replace all the line breaks with a space character.

Run this to "get" all characters permitted in a char() and varchar():

;WITH AllNumbers AS
(
    SELECT 1 AS Number
    UNION ALL
    SELECT Number+1
        FROM AllNumbers
        WHERE Number+1<256
)
SELECT Number AS ASCII_Value,CHAR(Number) AS ASCII_Char FROM AllNumbers
OPTION (MAXRECURSION 256)

OUTPUT:

ASCII_Value ASCII_Char
----------- ----------
1           
2           
3           
4           
5           
6           
7           
8           
9               
10          

11          
12          
13          

14          
15          
16          
17          
18          
19          
20          
21          
22          
23          
24          
25          
26          
27          
28          
29          
30          
31          
32           
33          !
34          "
35          #
36          $
37          %
38          &
39          '
40          (
41          )
42          *
43          +
44          ,
45          -
46          .
47          /
48          0
49          1
50          2
51          3
52          4
53          5
54          6
55          7
56          8
57          9
58          :
59          ;
60          <
61          =
62          >
63          ?
64          @
65          A
66          B
67          C
68          D
69          E
70          F
71          G
72          H
73          I
74          J
75          K
76          L
77          M
78          N
79          O
80          P
81          Q
82          R
83          S
84          T
85          U
86          V
87          W
88          X
89          Y
90          Z
91          [
92          \
93          ]
94          ^
95          _
96          `
97          a
98          b
99          c
100         d
101         e
102         f
103         g
104         h
105         i
106         j
107         k
108         l
109         m
110         n
111         o
112         p
113         q
114         r
115         s
116         t
117         u
118         v
119         w
120         x
121         y
122         z
123         {
124         |
125         }
126         ~
127         
128         €
129         
130         ‚
131         ƒ
132         „
133         …
134         †
135         ‡
136         ˆ
137         ‰
138         Š
139         ‹
140         Œ
141         
142         Ž
143         
144         
145         ‘
146         ’
147         “
148         ”
149         •
150         –
151         —
152         ˜
153         ™
154         š
155         ›
156         œ
157         
158         ž
159         Ÿ
160          
161         ¡
162         ¢
163         £
164         ¤
165         ¥
166         ¦
167         §
168         ¨
169         ©
170         ª
171         «
172         ¬
173         ­
174         ®
175         ¯
176         °
177         ±
178         ²
179         ³
180         ´
181         µ
182         ¶
183         ·
184         ¸
185         ¹
186         º
187         »
188         ¼
189         ½
190         ¾
191         ¿
192         À
193         Á
194         Â
195         Ã
196         Ä
197         Å
198         Æ
199         Ç
200         È
201         É
202         Ê
203         Ë
204         Ì
205         Í
206         Î
207         Ï
208         Ð
209         Ñ
210         Ò
211         Ó
212         Ô
213         Õ
214         Ö
215         ×
216         Ø
217         Ù
218         Ú
219         Û
220         Ü
221         Ý
222         Þ
223         ß
224         à
225         á
226         â
227         ã
228         ä
229         å
230         æ
231         ç
232         è
233         é
234         ê
235         ë
236         ì
237         í
238         î
239         ï
240         ð
241         ñ
242         ò
243         ó
244         ô
245         õ
246         ö
247         ÷
248         ø
249         ù
250         ú
251         û
252         ü
253         ý
254         þ
255         ÿ

(255 row(s) affected)
KM.
  • 101,727
  • 34
  • 178
  • 212
3

The specific characters that can be stored in a varchar or char column depend upon the column collation. See my answer here for a script that will show you these for the various different collations.

If you want to find all characters outside a particular ASCII range see my answer here.

Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
1

i think that special characters are # and @ only... query will list both.

DECLARE  @str VARCHAR(50) 
SET @str = '[azAB09ram#reddy@wer45' + CHAR(5) + 'a~b$' 
SELECT DISTINCT poschar 
FROM   MASTER..spt_values S 
       CROSS APPLY (SELECT SUBSTRING(@str,NUMBER,1) AS poschar) t 
WHERE  NUMBER > 0 
       AND NUMBER <= LEN(@str) 
       AND NOT (ASCII(t.poschar) BETWEEN 65 AND 90 
                 OR ASCII(t.poschar) BETWEEN 97 AND 122 
                 OR ASCII(t.poschar) BETWEEN 48 AND 57)
Harold Sota
  • 7,490
  • 12
  • 58
  • 84