How to Search for Apostrophes in T-SQL

Written by brendan mason
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Search for Apostrophes in T-SQL
(Thinkstock/Comstock/Getty Images)

Transact-SQL (T-SQL) has the ability to search for specific characters and strings within database fields, including special characters such as apostrophes. This example uses Microsoft SQL Server 2008 R2 and the AdventureWorks database, but other SQL Server editions use the same syntax.

Skill level:
Moderately Easy

Things you need

  • Installation of Microsoft SQL Server
  • Microsoft SQL Server Management Studio (or alternate T-SQL development environment)

Show MoreHide


  1. 1

    Click the Windows "Start" button and go to "All Programs." Go to the Microsoft SQL Server 2008 R2 folder, expand its contents and click "SQL Server Management Studio" to launch the development environment.

  2. 2

    Click the "Connect" button and connect to the server hosting the database.

  3. 3

    Expand the Databases folder within the Object Explorer window and right-click on the database to be queried. Select "New Query" to open a new T-SQL query window.

  4. 4

    Use "CHAR(39)" combined with wild card characters ("%") in the WHERE clause to query rows that contain apostrophes in a specific column. "CHAR(39)" in T-SQL represents character 39 in the ASCII character set, which is an apostrophe.

    For example, to select rows from the Contacts table in the AdventureWorks database that have LastName columns containing apostrophes anywhere in the column data, use the following query:

    SELECT *

    FROM [AdventureWorks].[Person].[Contact]

    WHERE LastName LIKE '%' + CHAR(39) + '%'

Tips and warnings

  • As an alternative to CHAR(39), a set of 4 apostrophes ('''') will achieve the same result, but CHAR(39) is generally preferred for readability.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.