0

I am trying to replace single quote characters in an inline SQL statement.

In SSMS:

SELECT REPLACE('test''test', '''', '')
Result: testtest

In Powerbuilder:

SELECT REPLACE(fieldname, '''', '')
INTO :ls_string
FROM tablename;
Result: 'Incorrect syntax near ''.

Basically, Powerbuilder complains about syntax because it doesn't know what to do with SQL's escape character. If I use Powerbuilder's escape character instead ('~'' versus '''' as the second argument for REPLACE), SQL Server complains about open quotes.

Any suggestions are appreciated.

Trashface
  • 27
  • 4

1 Answers1

0

You probably will have to either put the statement in a datawindow object (using the SQL quote escape method) which you use in the application as a datawindow or datastore OR do the SQL in a function or stored proc. If you go the function route you could call it in an imbedded SQL statement within Powerscript like your examples.

Something like

SELECT ufReplaceTwoQuotes INTO :ls FROM myTable USING SQLCA;

A third option would be to retrieve your data then do the replace within Powerscript (but then you have to wrestle with escaping the single quote within PowerBuilder).

Matt Balent
  • 2,337
  • 2
  • 20
  • 23
  • This looks like the best way to go in my example. It's probably a good idea to have a DB function for stripping special characters anyway. I used [this question's answer](https://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server) as a reference point. – Trashface Nov 09 '18 at 13:05