>>>Hello,
>>>I'm using a variable in the WHERE clause of a SQL Statement. The variable looks like so:
>>>cMyVar = "ALLTRIM(MyTable.MyField) = '" + ALLTRIM(THIS.VALUE) + "'"
>>>
>>>Naturally, if MyTable.MyField contains an apostroph (like "De'Isle"), I get a syntax error :-(
>>>What do I have to do in this case?
>>
>>You might try using the square brackets [] as delimiters within the string rather than the apostrophe, since they don't occur in the typical data; IOW:
>>
>>cMyVar = "ALLTRIM(MyTable.MyField) = [" + ALLTRIM(THIS.VALUE) + "]"
>>
>>Also, you're hurting yourself needlessly using ALLTRIM() in WHERE clauses; it ensures that the expression will not be Rushmore-optimizable, and depending on the SET environment, may result in unexpected matches using the = operator.
>>>Thanks in advance for your help!
>
>Hi Ed,
>Yes, the brackets are working fine.
>With the ALLTRIM(), I once had problems to match the values, if I don't use ALLTRIM. Is there another way to be sure, that the values match?

Assuming that you don't have leading spaces as an issue, I'd use:

cMyVar = 'MyTable.MyField == [" + PADR(this.Value, LEN(MyTable.MyField)) +']'

Which uses the exactly equal comparator, and ensures that the length ofd the compare string is the same as the length of the field.