On December 12th, I’ll be presenting on the DB2 Night Show on HADR and TSAMP Advanced Topics. Sign up here: http://www.dbisoftware.com/db2nightshow/

DB2 Basics: Quotation Marks

This blog entry may be a little on the basic side, but some of my most basic entries are some of my most popular ones.

DB2 has a number of special characters, for which there are rules of use. Both the single quotation mark and the double quotation marks are special characters. The DB2 Knowledge Center lists what DB2 considers special characters.

Using the Single Quote

For DB2, the single quotation mark or apostrophe – ' – delimits a string. Note that is a straight quote and not the curly kind that some programs like MS Word may use. Most frequently, I use the single quote in ways like these:

select * from schema.table where column1='some_string'
select tabschema || '.' || tabname from syscat.tables

But apostrophes or single quotes can also be a part of the data we work with. In order to escape an apostrophe, you simply use another apostrophe. Thus if you want to update a value with an apostrophe, you would use something like this:

update table set lastname = 'O''Hara' where id=1234

Using the Double Quote – Linux and Windows

The primary purpose of using double quotes is to prevent the operating system from interpreting whatever is between the double quotes. I generally wrap any db2 statement that I issue at the Linux/UNIX command line in double quotes.

This will work:

$ db2 "select count(*) from syscat.tables where type='T' with ur"

1
-----------
       1909

  1 record(s) selected.

While this will fail:

$ db2 select count(*) from syscat.tables where type='T' with ur
-bash: syntax error near unexpected token `('

A similar error from windows looks like this:

C:\Program Files (x86)\IBM\sqllib\bnd> db2 bind @db2ubind.lst blocking all sqlerror continue messages bind.msg grant 
public ACTION ADD 
At line:1 char:10 
+ db2 bind @db2ubind.lst blocking all sqlerror continue messages bind.msg grant pu ... 
+          ~~~~~~~~~ 
The splatting operator '@' cannot be used to reference variables in an expression. '@db2ubind' can be used only as an 
argument to a command. To reference variables in an expression use '$db2ubind'. 
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException 
    + FullyQualifiedErrorId : SplattingNotPermitted 

Curly Quotation Marks vs. Straight Quotation Marks

Some programs, such as MS Word, convert regular quotation marks to fancier looking curly quotation marks as you type. This can be annoying if you or a developer have text in such a program or use such a program in an intermediate step or to document a process. There are several ways these can come across. Sometimes, particularly if you’re not using unicode in your terminal, the single or double quotation marks are converted to periods. Other times, it looks like this:

$ db2 "select * from emspot where name = 'Lens_Recommendation_Modal_Espot’  with ur"
SQL0010N  The string constant beginning with
"'Lens_Recommendation_Modal_Espot’  with ur" does not have an ending string

Once the curly quote at the end of that string is corrected, the same statement runs just fine:

$ db2 "select * from emspot where name = 'Lens_Recommendation_Modal_Espot'  with ur"
DB20000I  The SQL command completed successfully.

If you need to escape a double quote, you double it just like with the single quotes. However, remember that your OS or shell may interpret double quotes, and that can lead to some unexpected behavior.

Did anyone doubt I could write 500 words on how to use a quotation mark?

3 comments… add one

  • Nice concise article. I like it. I found out the hard way about the single quote being an escape character. I didn’t know you can escape double quotes with a double quote. I have had to do some interesting shell/SQL scripting before. I always found it helpful to nest the different layers of quoting so I can tell which opens and closes which area and which escape character I need to use (DB2′s or the OS’s). Also helps to know the escape character for your OS. I work with AIX and KSH where it happens to be \.

    And good point on the copying and pasting of items out of Word. I *never, ever* work in Word anymore. I always use a plain text editor like Notepad++ so I don’t encounter issues like this. Reminds me of that code page conversion post you had a bit back (http://db2commerce.com/2013/05/14/what-to-do-with-a-character-you-cannot-query/). I’m sure that is part of what goes on there in the copy/paste from Word.

    Reply
    • I always work through TextMate when it’s just me – or vim if I really don’t need anything fancy. Love working on a Mac with those options. But many developers do love their Microsoft products.

      And when scripting, I work in perl, which also uses \. Scripting sure teaches you this stuff in spades.

      Reply
  • Very useful tips for MS-Word lover like me :) ,because of the problem I started using EditPlus, this topic covers basic but critical part.

    Reply

Leave a Comment