SQL Server varchar 字段中允许使用哪些“特殊”字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7038213/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Which "special" characters are allowed in SQL Server varchar fields?
提问by Harold Sota
Where can I see all the "special" characters permissible in a varcharor charfield in SQL Server?
我在哪里可以看到SQL Server 的avarchar或char字段中允许的所有“特殊”字符？
采纳答案by JNK
You probably just need to see the ASCIIand EXTENDED ASCIIcharacter sets. As far as I know any of these are allowed in a char/varcharfield.
您可能只需要查看ASCII和EXTENDED ASCII字符集。据我所知，char/varchar字段中允许使用这些中的任何一个。
If you use nchar/nvarcharthen it's pretty much any character in any unicode set in the world.
如果您使用nchar/nvarchar那么它几乎是世界上任何 unicode 集中的任何字符。
回答by KM.
EDITbased 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():
运行此命令以“获取” char() 和 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 a
171 ?
172 ?
173 -
174 ?
175 ˉ
176 °
177 ±
178 2
179 3
180 ′
181 μ
182 ?
183 ·
184 ?
185 1
186 o
187 ?
188 ?
189 ?
190 ?
191 ?
192 à
193 á
194 ?
195 ?
196 ?
197 ?
198 ?
199 ?
200 è
201 é
202 ê
203 ?
204 ì
205 í
206 ?
207 ?
208 D
209 ?
210 ò
211 ó
212 ?
213 ?
214 ?
215 ×
216 ?
217 ù
218 ú
219 ?
220 ü
221 Y
222 T
223 ?
224 à
225 á
226 a
227 ?
228 ?
229 ?
230 ?
231 ?
232 è
233 é
234 ê
235 ?
236 ì
237 í
238 ?
239 ?
240 e
241 ?
242 ò
243 ó
244 ?
245 ?
246 ?
247 ÷
248 ?
249 ù
250 ú
251 ?
252 ü
253 y
254 t
255 ?
(255 row(s) affected)
回答by Martin Smith
The specific characters that can be stored in a varcharor charcolumn depend upon the column collation. See my answer herefor a script that will show you these for the various different collations.
可以存储在varchar或char列中的特定字符取决于列排序规则。请在此处查看我的答案以获取脚本，该脚本将向您显示各种不同排序规则的这些内容。
If you want to find all characters outside a particular ASCII range see my answer here.
如果您想查找特定 ASCII 范围之外的所有字符，请在此处查看我的答案。
回答by Harold Sota
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)
