THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

Superscripts, subscripts, special characters, and Unicode

Office applications and SQL Server Management Studio (SSMS) handle superscripts and subscripts differently. Office apps such as Word allow you to assign font properties to make a character appear superscripted or subscripted. SSMS doesn’t support font properties, but Unicode does provide special characters for common superscripting and subscripting symbols.

Before you get the green light to build that SQL Server database, you’ll first have to make a compelling case for it using Word or PowerPoint. You want your proposal to look slick, full of superscripts, subscript, and Greek math symbols. That’s where we’ll start. Consider the following:

water is H20 and it boils at 100 C

That’s not very impressive. We can subscript the 2 by selecting it and right-clicking to bring up the Font dialog box. Select Superscript or Subscript to make your formatting change.

image       image

Figure 1. Font dialog box in Office applications.

If you have a lot of subscripting and superscripting to do, using the Font dialog box to do the formatting takes too long. It’s faster and simpler to use keyboard shortcuts. Highlight the character(s) of interest and use Ctrl = to subscript, Shift Ctrl = to superscript. Just so there is no confusion, = is the key on which both + and = appear. (It’s possible someone may prefer to describe Shift Ctrl = as Ctrl + using the logic that Shift = is the same thing as the + key.)

But even with the subscripting done, we’re still not fully done. We need the degree symbol. I did a search using Unicode degree symbol as my search string and discovered that it is the Unicode character that is hexadecimal 00B0. I find that it is faster to search for the hex code using a search engine instead of bringing up the dialog box that shows all of the Unicode character set and scrolling until I see what I need.

Once you know the hex value for the symbol you need, go to the Insert tab in Word and then select Symbol. Use the Symbol dialog box to enter the hex value for the symbol of interest.


Figure 2. Select More Symbols… to specify your symbol by its hexadecimal value.


Figure 3. Enter the hexadecimal value for your symbol into the Character code box.

Now your text looks quite professional and complete (although a scientist would feel compelled to mention STP).

water is H20 and it boils at 100° C

To add subscripting, superscripting, and special characters in SSMS strings, string concatenation using the NCHAR function is necessary.


Figure 4. Building strings with the NCHAR function.

SELECT N'H' + NCHAR(0x2082) + N'O';

SELECT N'Ca' + NCHAR(0x207A) +  NCHAR(0x207A);

SELECT N'E = mc' + NCHAR(0x00B2);

SELECT N'98.6' + NCHAR(0x00B0) + N'F';

SELECT NCHAR(0x03B2) + N'-blocker';

SELECT N'X ' + NCHAR(0x2265) + N' 6.0221415 * 10' + NCHAR(0x00B2) + NCHAR(0x00B3);

Try the preceding strings for yourself. The inverse of the NCHAR function is the UNICODE function, which returns a decimal value. You can use either decimal values or hexadecimal values with the NCHAR function. I showed mostly hexadecimal examples because I think you’ll most often find hex values when you are looking up the numeric value of a Unicode special character.


Figure 5. Working with decimal values.



SELECT N'H' + NCHAR(8322) + N'O';

NOTE: Some of the code samples may not render correctly depending on the capabilities and settings of your browser. Thanks to my fellow SQL Server MVPs Greg Low and Rob Farley for technical assistance in writing this post.

Using Unicode characters for superscripting and subscripting is particularly useful when markup languages can’t be used, such when posting in some social media platforms.

Published Sunday, October 30, 2011 10:51 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Rob Farley said:

You're welcome. :)

I think the most useful thing is for people to realise the usefulness of that field in the "Insert Symbol" box, so that if you can find the character you want in "(normal text)" font, then it will give you the value that you can use with the NCHAR() function.

Nice post.

October 30, 2011 11:43 PM

Kevin said:

There is a Character Map program that has the same functionality without having to use word. Usually located under Accessories|System Tools. So if you don't have office or don't want to start office for this functionality.

November 2, 2011 10:47 AM

John Paul Cook said:

Character Map certainly has its place, but it does require the user to scroll through a list of symbols. It is usually faster to use a search engine to find the Unicode value for a symbol. For example, if the greater than or equal to symbol is desired, you really have two choices for finding it. You can use "greater than or equal to symbol" as a search string and have the search engine find the Unicode value 0x2265. Or you can scroll through pages and pages of symbols until you find the symbol.

There are different needs at different times, so having more tools is good. Thanks for the suggestion.

November 2, 2011 11:46 AM

Eric Ness said:

One thing to be aware of is that under the default collation (case insensitive) the super-script two equals the non-superscript two.  Under case sensitive collation, though, these are unequal.  You can run this snippet to see the difference.

-- Case insensitive collation


   CASE WHEN NCHAR(0x2082) COLLATE SQL_Latin1_General_CP1_CI_AS = N'2' COLLATE SQL_Latin1_General_CP1_CI_AS THEN 'True'

        ELSE 'False'

   END AS Result

-- Case sensitive collation


   CASE WHEN NCHAR(0x2082) COLLATE SQL_Latin1_General_CP1_CS_AS = N'2' COLLATE SQL_Latin1_General_CP1_CS_AS THEN 'True'

        ELSE 'False'

   END AS Result

November 2, 2011 5:04 PM

Tavis Reddick said:

I think there may be confusion between markup and character sets here. Subscripts and superscripts are normally handled by markup, because there could be any content in them, not just specific numerals, for example. See HTML <sub> and <sup> Tags

Note also that sometimes superscripts are nested, which certain markup languages (say MathML) can handle.

A degree symbol is a specific character. There should be no problem handling the degree symbol in Unicode (see For HTML purposes, you can even encode unicode characters as entities (see HTML Symbol Entities Reference in more restricted character sets.

So, if you require to store rich text in SQL Server, it makes sense to store it (as Unicode) in a markup language such as HTML or an application of XML like WordprocessingML.

November 11, 2011 10:38 AM

John Paul Cook said:

There is no confusion about markup and character sets. HTML and XML are great for markup, which is not the subject of this post. There are certain Unicode characters that are specifically for use as subscripts and superscripts. This post shows how to find and use these Unicode characters.

Use markup languages when you can. Use Unicode when you can't. It all depends on what the requirements and constraints are.

November 11, 2011 10:38 PM

Tavis Reddick said:

@Paul, I did not mean to imply that you were confused, but there is overlap and uncertainty as to whether to use characters or markup, which is addressed by this W3C article:

Characters or markup?

which summarizes some recommendations, including that for "Superscripted and subscripted characters" their verdict was to "use <sup> or <sub> markup" instead of "¹ ² ³ ₁ ₂ ₃ " characters.

Although this article primarily applies to (X)HTML, the principle is similar in Word and MathML, for example. Markup allows differential selection and styling of content, which is good for accessibility.

Since the article mentions Microsoft Word and SQL Server, it is worth noting that Word's current format is based on a package of XML (markup) components, and SQL Server 2005 onwards supports the XML data type.

Our College happens to store XHTML markup (for course descriptions) in an ordinary character column, which can be transformed (using XSLT) into Word's markup language (for our prospectus extract) as well as slotted into HTML pages. If we stored subscripts in HTML markup, transforming to Word's XML format should be straightforward using pattern matching techniques which I would say are preferable to string methods used for character detection (and could still easily output super/subscript characters as required).

But as you say, it depends on requirements and constraints.

November 18, 2011 6:10 AM

Srini said:


  We have a typical problem where we have to send a supper script in Email Subject. As far as I know, Gmail does not support HTML Subjects, but supports Unicode characters.

  We have this SQL Server table dbo.Email where we need to insert Email Record with a superscript in Subject so that Our Email Agent (which uses Gmail) can sends the Email.

  The concepts explained in this page, suites to our problem very much and of course we resolved the issue with the help of this concepts. Thanks to John.

December 13, 2011 4:16 AM

Rajalukshmi R said:

Hi John

This is a pretty good one. Thanks for this.

I have a query that I need to dispaly service mark (SM) symbol in superscript. can you help me to do this?

March 5, 2012 1:49 AM

Maurice Frank said:

Thank you for a very informative and helpful article.

June 29, 2012 11:50 AM

dhaval patel said:

thanks for solution

September 19, 2012 8:27 AM

Peter Griffith said:

I have a table (760 rows) of descriptions in SQL Server of which a dozen rows contain superscripts subscripts and other maths notation.

I have defined the description field as nvarchar(200).

SQL Server is using default options.

How do i get these 12 descriptions from Word into SQL without losing the sub/superscripoting?

March 2, 2013 11:37 PM

John Paul Cook said:

If you paste the Word document into Excel, you can use the Import/Export Wizard to import the contents into a table in your database. Just make sure the columns you import into are either NCHAR or NVARCHAR. And of course, the Import/Export Wizard can extract from a tables to a file.

For the other direction, I did a qiuck test. I modified the above statements to insert the Unicode superscripting, subscripting, and special characters into a table. The table was selected into a grid in SSMS. I right-clicked in the grid and used Save Results As to save the results into a csv file. The formatting was preserved in Excel. The results were copied from Excel into Word and the formatting was still preserved. It is also possible to write from a database table directly into Word using .NET.

March 3, 2013 12:20 AM

Peter Griffith said:

Good stuff John,

 I now find that it is Excel losing the Sub/Superscripts.

 When I sort that out, it should all go.

 Thanks heaps.


March 3, 2013 5:03 PM

kasun said:

thank u very much.

April 23, 2013 12:39 AM

Kailash said:

i need to import excel data(all cell values) into a sql server table by using am able to import it by using .net codings.. but what the problem is i cant retrieve the chemical formulas in sql table as it is in the excel sheet... so how do i do this ??? plz tell me how to rectify and display as it is in the excelsheet including subscript and superscript ? ? ?

May 2, 2013 7:04 AM

Chaitra said:

I want to develop a tool that provides flexibility to user to write equations of any kind (Equations involving summation ,inverse wherein we need to give limits and even the equations involving symbols) for linux. But I do not know as what packages as to be imported. More importantly, is it possible to do using JAVA programming language? If not , what is that I have to do to fulfill the tools requirements ?

September 29, 2013 11:04 AM

Rajiv said:

sir, I want to store (/mm³)  inplace of 3 i want to store 5 in sqlserver 2005. what is the code of sqlserver.

my email id is

December 25, 2013 3:35 AM

Sneha said:

Thanks a lot, that was very useful.

February 17, 2015 5:13 AM

OpenSourcerer said:

Since MS is continuing to support Notepad...and regardless of the existence of Notepad++, and other "Notepad-like" Apps (see--e.g.-- should establish a means whereby user-suggested IMPROVEMENTS to this basic NP App, can be effected!...and beyond the means of MS' Help messaging window appended to the NP menu bar (that does not provide one-to-one/one-on-one feedback)!

And an example of a NP area in need of MS' attention, is MS' failure to aid  novice users of NP (within NP), of the features inhere within Character Map, and Unicode (the latter, re Alt Unicode rendering)! Why is...aiding novice NP users (within the NP menu) about these features (e.g., by offering new text box dialogue, and options, re Char Map, and Alt Unicode rendering), SACRILEGIOUS?

But further, Char Map and Unicode themselves, are--likewise--in need of some retooling.

In order to identify/use an unknown character/font found somewhere on the web/net, why can't one paste a found character/font into the "Search for: bar" within MS' Char Map, and...afterupon tapping linked to the font within MS' Char Map list of Fonts (in contrast, to the current method of linking a character to the font "pre-identified" within Char Maps' Font Scrollbar)?...or linked to the web source that identifies the font the character was rendered in, and be given a new Char Map menu option, and dialogue of instructions, as to how to download one's now identified (and sourced) font, into Char Maps' cache of Fonts, so that the new desired character/font that previously was not a part of Char Map's suite of fonts (and, incidentally, apart from initiating some Formatting Procedure by way of some Office App), can then be stored, and used?

Maybe...MS should buy out WhatTheFont...augment its features to include every symbol/character and Font known...and make this a reality! As goofing around with MS' Private Character Editor in order to fashion some required character (e.g., some elusive math or science symbol...and/or alphabetic superscript or the SS/SS letter "q") that isn't available through Char Map, or Unicode, is a pain in the Apps!

Lastly...and re the mention of the absence of the SS/SS letter "q" in Unicode specifically, and the suggested need for a retooling of Unicode...I'd like to bring attention to an article titled, "Proposal to encode missing Latin small capital and modifier letters in the UCS" (see URL: pdf+&cd=5&hl=en&ct=clnk&gl=us), that addresses this issue of the missing "q" in Unicode...and more.

For me--at least--the inability to render ALL of the alphabetic characters in Unicode, is ridiculous (let alone, the countless symbols one could and should be able to render as a SS/SS character in Unicode)! Formatting SS/SS through some Office Suite is fine (though, alignment within a Monospace environment, is--today--still an issue...i.e., SS/SS formatting, or the placement of boxes around characters, in Monospace, shouldn't be affecting the alignment of the "normal point size" characters, once invoked)...but, I--and, everyone else--should be able to immediately capture that unique symbol out there on the net (SS/SS, or otherwise), and incorporate such within the body of our chosen net medium, or personal file/s! And if Unicode is to play a role in that, then it MUST be able to "turn on a dime"!...and accommodate our proficient/prolific use of what's out there! Unicode must serve US!...not the other way around!

September 11, 2015 5:01 AM

Farhat said:

how to use st, nd, rd and th in superscript like 1st ,2nd ,3rd ,4th

November 6, 2015 6:45 AM

Leave a Comment


About John Paul Cook

John Paul Cook is a database and Azure specialist in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse currently studying to be a psychiatric nurse practitioner. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog



Privacy Statement