Replace a character with an empty string inside a dynamic SQL using OPENQUERY

Question

I need to replace a certain character inside a field with an empty string.

But this simple need becomes complicated because I need to extract data from a remote Oracle server and I need a parameter to reduce the amount of data returned.

So I need to use dynamic SQL with a properly constructed string.

This is the code I tried to solve the problem

DECLARE @sql varchar(4000) DECLARE @Period varchar(8) = '20250301' SET @sql = 'INSERT INTO [dbo].[TmpTable] (CustomerID) SELECT ORA.CustomerID FROM OPENQUERY(ORACLE_SERVER, '' SELECT -- this is where I want to remove the § character REPLACE(CustIdentifier, ''''§'''', '''') AS CustomerID FROM CustomerTable WHERE StartDate>= ''''' + @period + ''''' '') ORA' EXEC (@SQL)

As is, the code above returns this error:

OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_SERVER" returned message "ORA-01756: quoted string not properly terminated".

Msg 7323, Level 16, State 2, Line 2
An error occurred while submitting the query text to OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_SERVER".

Use PRINT @sql for troubleshooting. Doing so will show you have too many quotes. Try INSERT INTO [dbo].[TmpTable] ( CustomerID ) SELECT ORA.CustomerID FROM OPENQUERY(ORACLE_SERVER, '' SELECT -- this is where i want to remove the § character REPLACE(CustIdentifier, ''§'', '''') AS CustomerID FROM CustomerTable WHERE StartDate>= ''' + @period + ''' '') ORA' — Dan Guzman
– Dan Guzman, Commented Apr 16 at 16:49
Also, Oracle does not allow empty strings (converts empty strings to NULL). You might need to useeTO_DATE for the date literal depending on the Oralce column data type. — Dan Guzman
– Dan Guzman, Commented Apr 16 at 16:51
Why not remove it when you get it back on the SQL server side — siggemannen
– siggemannen, Commented Apr 16 at 17:33
Have you tried using Oracle's implementation of TRANSLATE()? While Microsoft SQL Server requires the same number of characters in the from_string and to_string parameters, on Oracle if there is no replacement character in the to_string parameter then matching from_string characters are deleted from the result, e.g.: SELECT TRANSLATE('_It''s full of §§§§§§§§§stars_', '_§', '_') FROM DUAL; db<>fiddle — AlwaysLearning
– AlwaysLearning, Commented Apr 16 at 21:33

sacse · Accepted Answer · 2025-05-03 12:34:29Z

I think you are missing some quotes within REPLACE.

try like below:

SET @sql = 'INSERT INTO [dbo].[TmpTable] (CustomerID) SELECT ORA.CustomerID FROM OPENQUERY(ORACLE_SERVER, '' SELECT REPLACE(CustIdentifier, ''''§'''', '''''''') AS CustomerID FROM CustomerTable WHERE StartDate>= ''''' + @Period + ''''' '') ORA'

printing both the queries show the difference.

Collectives™ on Stack Overflow

Replace a character with an empty string inside a dynamic SQL using OPENQUERY

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related