[转载]SQL Server中全半角字符的比较
<p>信息来源: 邪恶八进制信息安全团队</p><p>这是一个在我实际一个项目中遇到的问题。03BHL01001（上海）和03BHL01001(上海)比较的结果是一样的。导致引起的重复的主键问题。 </p><p /><p /><div id="3"> 03BHL01001（上海）和03BHL01001(上海)差别，就在于前者的括号是全角的括号字符，后者是半角的括号字符。全角的括号字符和半角的括号字符的ascii码显然是不一样的。全角的（ ASCII码是0xA3A8 ，而半角的( 是0x28。那么为什么SQL Server会认为是一样的呢？</div><p /><p /><div id="4"> 问题其实就出在数据库的排序规则上，让我们在仔细研读一下SQL Server的文档。SQL Server的排序规则由这样几部分组成，代码页、区分大小写、区分重音、区分宽度。最后一个在SQL Server的联机帮助中没有进一步提及，其实本篇遇到的问题就是由于这个原因造成的。区分宽度：指定 SQL Server 区分相同字符的单字节表示法（半角）和双字节表示法（全角）。如果没有选择，则 SQL Server 将认为相同字符的单字节表示法和双字节表示法等效。</div><p /><p /><div id="5"> 缺省，安装SQL Server中文版的时候，SQL Server帮你选择的排序规则是Chinese_PRC_CI_AS(Chinese-PRC, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive),是中文，不区分大小写、区分重音、不区分假名、不区分宽度。因此，自然就认同03BHL01001（上海）=03BHL01001(上海)。</div><p /><p /><div id="6"> 所以，正确的选择应该是，后缀为WS的中文排序规则。本例中我们应该选择Chinese_PRC_CI_AS_WS。</div><p /><p /><div id="7"> 我们来看一下，指定排序规则是Chinese_PRC_CI_AS_WS后，怎么样了？ </div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="8"><div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"> </span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">where</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">03BHL01001（上海）</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #808080">=</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">03BHL01001(上海)</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> collate Chinese_PRC_CI_AS_WS
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> （所影响的行数为 </span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> 行）
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span></div>
</div></pre></div><p /><div id="9"> 看来这个问题解决了。</div><p /><p /><div id="10"> 重要提示：</div><p /><p /><div id="11"> 如何察看使用那个排序规则呢？可以使用下面的SQL语句。</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="12">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"> </span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"> </span><span style="COLOR: #0000ff">FROM</span><span style="COLOR: #000000"> ::fn_helpcollations()</span></div>
</div></pre></div><p /><div id="13"> 可以查询所有排序规则的信息。</div><p /><p /><div id="14"> 查出所有中文排序规则的信息</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="15">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000"> </span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">FROM</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> (
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="COLOR: #0000ff">FROM</span><span style="COLOR: #000000"> ::fn_helpcollations()) A
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /> </span><span style="COLOR: #0000ff">WHERE</span><span style="COLOR: #000000"> name </span><span style="COLOR: #808080">like</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">Chinese%</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span></div>
</div></pre></div><div id="ParagraphCount" style="DISPLAY: none">15</div>
页:
[1]