0

I am trying to deal with an apostrophe in a prompt field in my SQL statement. Example below :

WHERE propertyfieldintable=@promptfromuser

The problem is that there are addresses with an ' eg. Wood's therefore the SQL works for all other addresses except those with an '.

I have tried to use replace(@promptfromuser,"'","''") but keep getting an error (missing right '). The propertyfieldintable is a text field so am assuming it needs a text input from the prompt.

I would appreciate any straightforward suggestions as the query is not in the database itself but sits within a reporting tool so can't take functions.

BoltClock
  • 700,868
  • 160
  • 1,392
  • 1,356
JAE
  • 1
  • Apostrophes are accepted in parameters without using REPLACE. What are your exact errormessage? – Alex Dec 20 '10 at 12:35
  • You really ought to be using paramaterised SQL ... have a read of this stackoverflow question: http://stackoverflow.com/questions/3216233/what-is-passing-parameters-to-sql-and-why-do-i-need-it/3216331 – Chris J Dec 20 '10 at 13:17

2 Answers2

2

You need to use parameters.

SLaks
  • 868,454
  • 176
  • 1,908
  • 1,964
0

Try the following replace statement

replace (@promptfromuser,'''', '''''')

Forget using " in databasecontext for ever. Microsoft has [] syntax to denote identifiers containing special characters.

bernd_k
  • 11,558
  • 7
  • 45
  • 64