Thursday, June 20, 2013

You CAN use quotes as a text value delimiter in T-SQL

SET QUOTED_IDENTIFIER OFF

Even though I said in a recent post (Access SQL Delimiters vs. T-SQL Delimiters), that T-SQL uses only the apostrophe (') as a text delimiter, that's not entirely true. It IS possible to use the quote (") as a text delimiter, just like Access. To do this, you must set a T-SQL option called QUOTED_IDENTIFIER to OFF. This must be done at the beginning of the pass-through query:

SET QUOTED_IDENTIFIER OFF

SELECT MyTextField, MyDateField, MyIntField
FROM dbo.MyTable
WHERE MyTextField="Roger Carlson";

SET QUOTED_IDENTIFIER ON

Because T-SQL (and therefore pass-though queries) allow multiple statements to execute, you can turn the option off, and then back on again in the same query.

Please note that I DID set the option back on at the conclusion of the query. That's because it will set it OFF for every pass-though query in this Access session, and you may not want that. It is always best to set an option back the way it was to avoid confusion.

This can be useful when you are converting an Access SQL statement (that has a lot of delimited text values) to T-SQL.

SELECT MyTextField, MyDateField, MyIntField
FROM dbo.MyTable
WHERE MyTextField IN ("Roger Carlson", "Sue Carlson", "Bob O'Brien");

Instead of replacing the quotes with apostrophes (remembering that Access Query Builder does not have a find and replace feature), you can simply bracket it with the QUOTED_IDENTIFIER option.

SET QUOTED_IDENTIFIER OFF

SELECT MyTextField, MyDateField, MyIntField
FROM dbo.MyTable
WHERE MyTextField IN ("Roger Carlson", "Sue Carlson", "Bob O'Brien");

SET QUOTED_IDENTIFIER ON

Monday, June 17, 2013

Access SQL Delimiters vs. T-SQL Delimiters

In my earlier post: What are the differences between Access SQL and T-SQL?, I discussed in the differences between Access SQL and T-SQL in general terms. This time, I want to expand upon the differences in how delimiters are used between the two.

Delimiters are generally used around explicit values in SQL statements. By explicit value, I mean a value in one of the fields upon which you are searching. For instance, if I wanted to find the value "Roger Carlson" in a field in my table, I would surround the value in quotes (in Access), as I'll demonstrate a little later on. Different types of data require different delimiters.

As seen in my previous post, it breaks down like so:

Delimiters

Type

  Access

  T-SQL

  Numeric

  no delimiter

  no delimiter

  Text

  " or ' (quote or apostrophe)

  ' (apostrophe)

  Date

  # (pound sign)

  ' (apostrophe)

  Wildcard

  *

  %

  Delimited Identifiers

  [..]

  [..] or “..”

Note: For all of the samples I'll be using a simple SQL Server table. Access queries will be against a linked table. T-SQL statement will be as pass-through queries executed in Access. As such, table names in the Access queries will start with 'dbo_', whereas T-SQL table name with have 'dbo.'.

 

dbo_MyTable

MyTextField

MyDateField

MyIntField

Roger Carlson

1/1/2013

44

Susan Carlson

2/13/2013

88

Bob O'Brien

4/16/2013

0

Numeric

Numeric values do not require a delimiter at all, in either Access SQL or T-SQL

Access SQL

SELECT MyTextField, MyDateField, MyIntField
FROM dbo_MyTable
WHERE MyIntField>=44;

T-SQL

SELECT MyTextField, MyDateField, MyIntField
FROM dbo.MyTable
WHERE MyIntField>=44;

The SQL statements are identical and they produce identical output:

MyTable

MyTextField

MyDateField

MyIntField

Roger Carlson

1/1/2013

44

Susan Carlson

2/13/2013

88

 

Text

Text values do require a delimiter.

Access SQL

The delimiter is either a quote (") or an apostrophe (')

SELECT MyTextField, MyDateField, MyIntField
FROM dbo_MyTable
WHERE MyTextField="Roger Carlson";

Or

SELECT MyTextField, MyDateField, MyIntField
FROM dbo_MyTable
WHERE MyTextField='Roger Carlson';

This can be useful when the field value itself has a delimiter in it. For instance, if the name is Bob O'Brien, using the quote delimiter will not produce an error, whereas an apostrophe would.

SELECT MyTextField, MyDateField, MyIntField
FROM dbo_MyTable
WHERE MyTextField="Bob O'Brien";

(There are of course, other solutions for this, but that's beyond the scope of this post.)

T-SQL

Generally, the only delimiter used is the apostrophe (') -- although there is an exception, which I'll discuss below.

SELECT MyTextField, MyDateField, MyIntField
FROM dbo.MyTable
WHERE MyTextField='Roger Carlson';

To query a name like O'Brien, you'd double the explicit apostrophe (one of those "other" solutions I mentioned).

SELECT MyTextField, MyDateField, MyIntField
FROM dbo.MyTable
WHERE MyTextField= 'Bob O''Brien';

Note that this is TWO apostrophes, not a quote.

 

Dates

The date delimiters between Access SQL and T-SQL are also different.

Access SQL

Access uses the pound or hash mark (#).

SELECT MyTextField, MyDateField, MyIntField
FROM dbo_MyTable
WHERE MyDateField>=#2/1/2013#;

T-SQL

T-SQL uses the apostrophe ('), just like text values.

SELECT MyTextField, MyDateField, MyIntField
FROM dbo.MyTable
WHERE MyDateField>='2/1/2013';

In either case, the query engine will interpret the explicit date in US date format, so the above it February 1st, not January 2nd. If the date format is an issue, it's best to use the YYYY/MM/DD format.

SELECT MyTextField, MyDateField, MyIntField
FROM dbo.MyTable
WHERE MyDateField>='2013/02/01';

 

Wildcards

Both Access SQL and T-SQL can use wildcards with the LIKE operator to allow the user to query for a character or group of characters anywhere within a text field. For instance, say if I wanted to know just the Carlsons in the table.

Access SQL

In Access, the wildcard most used is the asterisk (*).

SELECT MyTextField, MyDateField, MyIntField
FROM dbo_MyTable
WHERE MyTextField Like "*Carlson";

The question mark can be used to act as a wild card for a single character only.

T-SQL

T-SQL uses the percent character (%).

SELECT MyTextField, MyDateField, MyIntField
FROM dbo.MyTable
WHERE MyTextField LIKE '%Carlson';

 

Delimited Identifiers

So far, I've only discussed delimiters in regards to explicit values. However, identifiers (that is, table names and field names) also must be delimited when they contain illegal characters like a space or question mark or others. In order for the query engine to recognize a tablename or fieldname with spaces (or other illegal characters) in it, is to delimit it.

Access SQL

In Access, the delimiters for identifiers are the right and left brackets ([…])

SELECT [My Text Field], [My Date Field], [My Int Field]
FROM [dbo_My Table]
WHERE [My Int Field] = 44;

T-SQL

T-SQL has two delimiters available. Left and right brackets ([…]), just like Access, but it can also use the quote marks ("…").

SELECT [My Text Field], [My Date Field], [My Int Field]
FROM dbo.[My Table]
WHERE [My Int Field] = 44;

Or

SELECT "My Text Field", "My Date Field", "My Int Field"]
FROM dbo."My Table"
WHERE "My Int Field" = 44;

Invalid Column Name Error

Now, at best, using the quotes as identifier delimiters is a curiosity, except it explains the odd error message you get when you try to execute an Access query with text identifiers in T-SQL.

If I try to run this query as a pass-through query,

SELECT MyTextField, MyDateField, MyIntField
FROM dbo_MyTable
WHERE MyTextField="Roger Carlson";

I'll get the following message:

clip_image002

Notice that the error is an "Invalid column name". That's because it thinks "Roger Carlson" is a column, not an explicit value.