Search code examples
sql-serversql-server-2014

SQL Server data patch for replacing 2 double quotes in between to single double quote

I am currently trying to do data patch.

Consider this example: every day, after nightly process is successful, I am getting borrower name somewhat different. If borrower name has single quote in between the string, I am getting value as ("").

For example, if the record has value JAN E. O"BRIEN, then every night, I am getting the result as JAN E. O""BRIEN.

I can update the previous data by simple update. But, every night, I am getting the same error. So, I would like to update my stored procedure logic with some Case Statement in order to handle such escape character situation.

Can anyone please help me resolve this issue in SQL Server? (P.S. I am using SQL Server 2014)

Solution

  • You can try to use a simple replace when you are retrieving the values.

    Some think like: 

    Select Replace(FieldName ,'""','"') as FieldName from table..

    I figure that another statement is updating every day the value inserting ("").