[SQL Server] 特殊字符、上标、下标处理

今天遇到一个问题是往 SQL Server 中导入像m²、m³这样的单位数据，可是在 SQL Server 中查看到的都是 m2、m3，于是在网上查了一下资料，顺便摘录下来供日后查阅。

一  Windows 系统下快速键入一些特殊字符

ALT+小键盘0153        ™

ALT+小键盘0169        ©

ALT+小键盘0174        ®

ALT+小键盘0177        ±

ALT+小键盘0178        ²

ALT+小键盘0179        ³

ALT+小键盘0188        ¼

ALT+小键盘0189        ½

下面是从 http://www.wikihow.com/Type-Symbols-Using-the-ALT-Key 查到的特殊字符键入方法总结：

特殊字符展开

SYMBOL	NAME	CODE (ALT +)	SYMBOL	NAME	CODE (ALT +)
©	Copyrighted	0169	♄	Saturn	9796
®	Reserved mark	0174	♅	Uranus	9797
℠	Service mark	8480	♆	Neptune	9798
℃	Celsius	8451	♇	Pluto	9799
℅	Care of	8453	♈	Aries	9800
℉	Fahrenheit	8457	♉	Taurus	9801
№	Number symbol	8470	♊	Gemini	9802
℗	Sound recording copyright	8471	♋	Cancer	9803
℞	Prescription/pharmaceutical symbol	8478	♌	Leo	9804
Ω	Ohm	8486	♍	Virgo	9805
℧	Inverted ohm	8487	♎	Libra	9806
☀	Sunshine	9728	♏	Scorpio	9807
☁	Cloud	9729	♐	Sagittarius	9808
☂	Umbrella	9730	♑	Capricorn	9809
☃	Snowman	9731	♒	Aquarius	9810
☄	Comet	9732	♓	Pisces	9811
★	Star (solid)	9733	♔	White king	9812
☆	Star (outline)	9734	♕	White queen	9813
☇	Lightning	9735	♖	White rook	9814
☈	Thunderstorm	9736	♗	White bishop	9815
☉	Sun	9737	♘	White knight	9816
☊	Ascending node	9738	♙	White pawn	9817
☋	Descending node	9739	♚	Black king	9818
☌	Conjunction	9740	♛	Black queen	9819
☍	Opposition	9741	♜	Black rook	9820
☎	Phone (solid)	9742	♝	Black bishop	9821
☏	Phone (outline)	9743	♞	Black knight	9822
☐	Checkbox	9744	♟	Black pawn	9824
☑	Ballot box	9745	♠	Black spade suit	9824
☒	Ballot box with X	9746	♡	Red heart suit	9825
☓	Saltire (St. Andrew's Cross)	9747	♢	Red diamond suit	9826
☚	Left-pointing index finger (solid)	9754	♣	Black club suit(or shamrock)	9827
☛	Right-pointing index finger (solid)	9755	♤	Red spade suit	9828
☜	Left-pointing index finger (outline)	9756	♥	Black heart suit (or valentine)	9829
☝	Upwards-pointing index finger (outline)	9757	♦	Black diamond suit	9830
☞	Right-pointing index finger (outline)	9758	♧	Red club suit	9831
☟	Downwards-pointing index finger (outline)	9759	♨	Hot springs	9832
☠	Skull & crossbones	9760	♩	Musical quarter note	9833
☡	Caution sign	9761	♪	Musical eighth note	9834
☢	Radioactive sign	9762	♫	Musical single bar note	9742
☣	Biohazard sign	9763	♬	Musical double bar note	9836
☤	Caduceus or "Kerykeion"	9764	♭	Flat note	9837
☥	Ankh	9765	♮	Natural note	9838
☦	Eastern Christian cross	9766	♯	Sharp note	9839
☧	Chi Rho cross	9767	✁	Cut above	9985
☨	Patriarchal cross	9768	✂	Cut here	9986
☩	Greek cross	9769	✃	Cut below	9987
☪	Crescent moon & star	9770	✄	Scissors	9988
☫	Farsi symbol	9771	✆	Public pay phone	9990
☬	Adi Shakti	9772	✇	Film reel (tape spool)	9991
☭	Hammer & sickle	9773	✈	Airport/airplane	9992
☮	Peace sign	9774	✉	Envelope/mail/email	9993
☯	Yin & yang	9775	✌	Victory sign	9996
☰	Trigram Heaven	9776	✍	Signature/sign here	9997
☱	Trigram Lake	9777	✎	Pencil diagonal down	9998
☲	Trigram Fire	9778	✏	Pencil	9999
☳	Trigram Thunder	9779	✐	Pencil diagonal up	10000
☴	Trigram Wind	9780	✓	Checkmark	10003
☵	Trigram Water	9781	✔	Heavy checkmark	10004
☶	Trigram Mountain	9782	✕	Multiplication sign/X	10005
☷	Trigram Earth	9783	✖	Heavy multiplication sign/X	10006
☸	Dharma wheel	9784	✗	Ballot X	10007
☹	Frowning face	9785	✘	Heavy ballot X	10008
☺	Smiley face	9786	✝	Latin Roman cross	10013
☻	Black smiley face	9787	✞	Latin cross 3D shadow	10014
☽	Waxing crescent moon	9789	✟	Latin cross outline	10015
☾	Waning crescent moon	9790	✠	Maltese Cross	10016
☿	Mercury	9791	✡	Star of David	10017
♀	Venus (female symbol)	9792	❛	Quotation mark single comma	10075
♁	Earth symbol	9793	❜	Quotation mark single comma	10076
♂	Mars (male symbol)	9794	❝	Quotation mark double turned comma	10077
♃	Jupiter	9795	❞	Quotation mark double comma	10078

返回顶部收缩

二  SQL 中查询和插入特殊字符数据

举个例子：

-- 不带N
INSERT INTO Table1(Unit) VALUES('m²');
-- 带N
INSERT INTO Table1(Unit) VALUES(N'm²');

唯一区别是第二句在含有特殊字符的字符串前面加了一个 N ，两句导入的结果就是，前一句的数据在 SQL Server 中是 m2，后者看到的才是 m²。

同样，要查找的时候也是要在含特殊字符的字符串前面加上一个 N 才可匹配。

SELECT Unit FROM Table1 WHERE Unit = N'm²';

 

三  参考资料

1、http://www.2cto.com/database/201308/235481.html

2、http://www.wikihow.com/Type-Symbols-Using-the-ALT-Key