CODEWITHSUNDEEP
sqlsql-server
user6114185
user6114185

Reputation: 27

Trouble with apostrophe in stored procedure

I have the following case statement in sql

case when fake.join in (''Women'') then ''Women's'' else ''Other'' end'

in my stored procedure. I'm having trouble getting it to come through as "Women's" and the only thing i can find to deal with this says to do ''Women''s'') but that makes the apostrophe disappear. I need to keep the apostrophe. Please help! Thanks

Upvotes: 0

Views: 1082

Answers (2)

morgb
morgb

Reputation: 2312

If this is truly a standalone statement, then it should look like this:

case when fake.join in ('Women') then 'Women''s' else 'Other' end

If you're building a string with some dynamic SQL (which is kind of what it looks like with all the apostrophe escaping), it should look like this:

case when fake.join in (''Women'') then ''Women''''s'' else ''Other'' end'

EDIT: Here it is with the fuller snippet you provided (just note the 4 apostrophe's between the 'n' and 's'): 

case when sm.LegalName in (''Brigham & Women Hospital'', ''Brigham & Women Faulkner Hospital'') then ''Brigham & Women''''s Hospital'' 
    when sm.LegalName in (''Newton Wellesley Hospital'', ''Massachusetts General Hospital'') then sm.LegalName 
    else ''Other'' end'

You can then feed a string including this to sp_executesql and it should give you what you're after.

Upvotes: 5

Tab Alleman
Tab Alleman

Reputation: 31795

If you want the resulting string to be 'Women's', then use '''Women''s''':

PRINT '''Women''s'''

Results in:

'Women's'

Upvotes: 1

Related Questions