SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pulling data with apostrophe
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wsilage
Yak Posting Veteran

USA
54 Posts

Posted - 08/06/2013 :  13:55:29  Show Profile  Reply with Quote
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

India
616 Posts

Posted - 08/06/2013 :  14:02:59  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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

Edited by - ahmeds08 on 08/06/2013 14:04:07
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 08/06/2013 :  14:04:05  Show Profile  Reply with Quote
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/

Edited by - James K on 08/06/2013 14:05:16
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30178 Posts

Posted - 08/06/2013 :  14:50:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

India
22754 Posts

Posted - 08/18/2013 :  12:33:36  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000