Contains syntax help - where search term contains an apostrophe (1 Viewer)

[Isaac]

CONTAINS(doc.Result,  '"Unit 2 of 2 PRBC's completed" | "phrase two" | "phrase three"')

Okay ... So I use Contains quite a bit in my T-SQL queries, and to start with here, I know that the above code is perfectly correct IF there was not an apostrophe in the search term (PRBC's).
In other words I'm certain that the various quotes are all correct but only IF I didn't have an apostrophe; I"m not sure what changes are indicated as I do.

I've discovered that Contains is quite the amazing thing, with literally dozens and dozens of ways to structure it, such that you could probably just about replace an NLP model in another language with a well structured series of Contains.

Every time I "poke" this bear, he gets messed up. What is the correct solution to transform what I have above into something that will accept the apostrophe? I did try MSDN and stack overflow; but the former as you will know if you've tried this on Contains would take, like, a year to master, and the latter is rife with too-specific different scenarios other than mine.

 

[Isaac]

BTW - I did try doubling up the apostrophe and that's it. My result was hard to gauge, the syntax doesn't throw an error which is nice, but I get zero results - but in this particular use case, zero results may be correct, so I'm having a hard time testing.

Do you think doubling up is good enough?

 

[SQL_Hell]

You could try something like this:

declare @string varchar(100)
set @string = '"Unit 2 of 2 PRBC'+char(39)+ 's completed" | "phrase two" | "phrase three"'
print @string

CONTAINS(doc.Result, @string)

 

[Isaac]

You could try something like this:

declare @string varchar(100)
set @string = '"Unit 2 of 2 PRBC'+char(39)+ 's completed" | "phrase two" | "phrase three"'
print @string

CONTAINS(doc.Result, @string)

I thought Contains will only work it's truest magic on something with a full text index - that's why it's hard for me to quickly rig up a test

But let me play with that a little, thx

 

[gemma-the-husky]

given this

'"Unit 2 of 2 PRBC's completed"

You have conflicting pairs of quotes (1 pair of simgle quotes, and 1 pair of double quotes)
I expect that may cause the problem.

I don't know whether you can get around it by using

chr(34) & mystring & chr(34) OR
chr(39) & mystring & chr(39)

I imagine if you knew which char mystring contain/did not contain, you could get around it with this sort of thing.
If it contains both, it will most likely need some testing. not sure if the aircode syntax is spot on.

if instr(mystring, chr(34)) and instr(mystring, chr(39)) then
    msgbox "too complex"
else
     if instr(mystring, chr(34)) then
          use chr(39) & mystring & chr(39)
     end if
     else
              use mystring
              'maybe you need 
              'use chr(34) & mystring & chr(34)
     end if

 

[cheekybuddha]

Haven't read this all the way through, but it might shed some light?

 

[Isaac]

Thanks gentlemen I ended up testing with simply escaping it as doubling up and it seemed to work.
I tested it by querying our documentation for things like: Patient's chart which I knew must exist and it brought them back w/out err-ing.

Dave I know the singles next to doubles look funny but that's legit in this case - I use it alot as-is with the only difference being the PRBC's in the mix

 

[SQL_Hell]

given this

'"Unit 2 of 2 PRBC's completed"

You have conflicting pairs of quotes (1 pair of simgle quotes, and 1 pair of double quotes)
I expect that may cause the problem.

I don't know whether you can get around it by using

chr(34) & mystring & chr(34) OR
chr(39) & mystring & chr(39)

I imagine if you knew which char mystring contain/did not contain, you could get around it with this sort of thing.
If it contains both, it will most likely need some testing. not sure if the aircode syntax is spot on.

if instr(mystring, chr(34)) and instr(mystring, chr(39)) then
    msgbox "too complex"
else
     if instr(mystring, chr(34)) then
          use chr(39) & mystring & chr(39)
     end if
     else
              use mystring
              'maybe you need
              'use chr(34) & mystring & chr(34)
     end if

You only need to worry about the double quotes if SET QUOTED_IDENTIFIER is set to on, the default is off in SSMS.

 

[Isaac]

Contains is a little different. You use double quotes with a phrase regardless