Question 1:

I am currently reading Transact-SQL by Bennett McEwan. I was trying to do a query on the pubs test database...

select "Hello, my name is ", fname
from employee

No matter how I word the query it keeps bombing out and saying...

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'hello, my name is'.

Is the command structure for "Constant Strings" different for SQL 2000?

Question 2:

Let's say I have a table named "STAFF". One of the fields is "NAME". Now, if I have a staff name "Mohd Ja'far" then how can I retrieve this data using SQL. The query:

SELECT * FROM STAFF 
WHERE NAME LIKE '%Ja'fa%'

is not working because the ' in the name.


    Requires Free Membership to View

    Login

A number of questions seem to come up again and again here at SearchDatabase.com. Removing duplicate rows and finding the "top N" rows are probably the most frequently asked questions, and well worth the effort to explore, because of the implications on database design, application controls, data integrity, user interfaces, and so on.

Questions that deal with simple things like quotes in SQL are also quite common, and most of the time we do not answer them. They're like a rite of passage into the wonderful world of self-reliance, since they can usually be resolved with a healthy dose of RTFM.

Nevertheless, we've all encountered manuals where the single item you're trying to find is buried if it's there at all. So today, we'll answer these questions on quotes in SQL.

In the first question, SQL Server expects the doublequote to be used to delimit a column name that otherwise would cause a syntax error, because of embedded spaces or other characters. Literal strings must be enclosed in single quotes. Thus, the correct syntax is:

select 'Hello, my name is ', fname
from employee

An example of the doublequote used to delimit a column name is:

select Sum10 - Sum09 as "Oct/Sept Increase"
from sales

Note that SQL Server can also use square brackets for this purpose:

select Sum10 - Sum09 as [Oct/Sept Increase]
from sales

In the second question, the problem is that the literal string needs to contain a single quote. This is achieved by coding two single quotes in succession, and this works in all databases:

WHERE NAME LIKE '%Ja''fa%'

This was first published in October 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top