InStr in Select statement

Question

How can I select all names from a field in a SQL Server table that includes an "'" in the field?  The field is named [Patient ID] and the table is named "tblPats".  Thanks!

Answer 1

If you actually mean an Access Query using ODBC Linked Tables sourced from SQL Server then you you can use double-quotes as Text/String delimiter and therefore, you can use an SQL like:

SELECT tblPats.[Patient ID], tblPats.FirstName, tblPats.LastName
FROM tblPats
WHERE tblPats.LastName Like "*'*"

* is the wildcard for any sequence of characters.

---

Van Dinh

Answer 2

However, if you're running the query as a pass-through (so that it runs directly on the server), or if you've set ANSI 92 compatibility, or if you're using ADO, the wild-card character is %:

SELECT tblPats.[Patient ID], tblPats.FirstName, tblPats.LastName
FROM tblPats
WHERE tblPats.LastName Like "%'%"

Actually, if this is a pass-through query to SQL Server, the text delimiter is a single quote, not a double quote, so the SQL would be:

SELECT tblPats.[Patient ID], tblPats.FirstName, tblPats.LastName
FROM tblPats
WHERE tblPats.LastName Like '%''%'

(note the two single quotes in a row to handle the embedding of a single quote within single quotes)

---

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

Answer 3

Perfect!  Thanks!

Ken

Answer 4

Doug,

This is very helpful.  Thanks.

Ken