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

时间:2024-05-03 08:03:49

今天遇到一个问题是往 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