THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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.

image

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

image

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.

image

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.

image

Figure 5. Working with decimal values.

 

SELECT UNICODE(N'₂');

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

Comments

 

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

SELECT

   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

SELECT

   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

http://www.w3schools.com/tags/tag_sup.asp

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 http://en.wikipedia.org/wiki/Degree_symbol). For HTML purposes, you can even encode unicode characters as entities (see HTML Symbol Entities Reference

http://www.w3schools.com/tags/ref_symbols.asp) 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?

http://www.w3.org/International/questions/qa-chars-vs-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:

Tavis,

  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.

PG

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 asp.net... 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 rajivnayan7@gmail.com

December 25, 2013 3:35 AM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a Technology Solutions Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft 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 who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement