Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pulling data with apostrophe

Author  Topic 

wsilage
Yak Posting Veteran

82 Posts

Posted - 2013-08-06 : 13:55:29
I am having a problem trying to pull data that has apostrophe in them.

How can I do this? I get this as an error

Msg 105, Level 15, State 1, Line 14
Unclosed quotation mark after the character string ''.


Select

Name

From Table
Where Name IN (CHILDREN'S ANES ASSOCS-CHOP,CHILDREN'S HEALTHCARE-CHOP,CHILDREN'S PSYCH ASSOC-CHOP,CHILDREN'S SURGICAL ASSOC-CHOP)

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-08-06 : 14:02:59
can u try this


Select

Name

From Table
Where Name IN ('CHILDREN''S ANES ASSOCS-CHOP','CHILDREN''S HEALTHCARE-CHOP','CHILDREN''S PSYCH ASSOC-CHOP','CHILDREN''S SURGICAL ASSOC-CHOP')


mohammad.javeed.ahmed@gmail.com
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-06 : 14:04:05
Escape each instance of single quote that is part of the data with another single quote. Also, your IN clause should enclose each search item in single quotes rather than a comma-separated list within single quotes:
Select

Name

From Table
Where Name IN
(
'CHILDREN''S ANES ASSOCS-CHOP',
'CHILDREN''S HEALTHCARE-CHOP',
'CHILDREN''S PSYCH ASSOC-CHOP',
'CHILDREN''S SURGICAL ASSOC-CHOP'
)


Editing: Forgot to plug a shallow SQL blog http://myshallowsqlblog.wordpress.com/escaping-single-quotes-in-sql/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-06 : 14:50:28
Are the options the result of a string concatenation and you plan to execute the dynamically built string?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-08-18 : 12:33:36
Also read this post http://beyondrelational.com/modules/2/blogs/70/posts/10827/understanding-single-quotes.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -