Brettb.Com
  HOME | ABOUT ME | BIOTECHNOLOGY | ARTICLES | GALLERY | CONTACT
Search: Go
TECHNICAL ARTICLES
 ASP
 ASP.NET
 JavaScript
 Transact SQL
 Other Articles
 Software Reviews

PHOTO GALLERIES
 Canon EOS 300D Samples
 Akihabara Maids!
 More Galleries...

TRAVEL LOG
 2009: China
 2008: Tokyo
 2007: Tokyo
 2006: Hong Kong
 2005: New York City

MORE STUFF
 Search Engine Optimisation
 Build an ASP Search Engine
 My Tropical Fishtank
 Autoglass
 SQL Month Name
 SQL Get Date Today
 SQL Year Month
 Other New Stuff...

POPULAR STUFF
 Regular Expressions
 Index Server & ASP
 JavaScript Ad Rotator

Home > Articles > Transact SQL Programming Articles

SQL Server Help: How to use apostrophes in Transact-SQL

Since strings are normally enclosed within single quotes in Transact SQL, it can be a problem if the string's text contains an apostrophe within it, such as the query below:

SELECT *, FROM Products WHERE ProductName = 'King's Jalepenos'

Obviously this SQL query would fail due to the apostrophe in the ProductName. Thankfully though, the solution is simple - just put two apostrophes in the string, as shown below:

SELECT *, FROM Products WHERE ProductName = 'King''s Jalepenos'

Replacing the single quotes with two single quotes should be done wherever a string that may potentially contain a single quote is used within a query. This can be achived in most programming languages by using a Replace function. For example, in Visual Basic.NET the syntax to use would be the following:

Dim ProductName As String = "King's Jalepenos"
Dim SQL As String
SQL = "SELECT *, FROM Products WHERE ProductName = '" & Replace(ProductName, "'", "''") & "'"

Similarly, in C# the Replace function can be used in an identical way:

Dim ProductName As String = "King's Jalepenos"
Dim SQL As String
SQL = "SELECT *, FROM Products WHERE ProductName = '" & Replace(ProductName, "'", "''") & "'"

If your strings are being obtained from user input (e.g. from a web form) then don't forget to remove other characters from strings as well in order to guard against SQL injection attacks. See the article guarding web applications against sql injection attacks for more information.

Useful Links

  • The SQL Documentation Tool automatically builds technical documentation for Microsoft SQL Server databases, saving you time and money. A trial version is available for download.

  Site Map | Privacy Policy

All content is 1995 - 2012