CODEWITHSUNDEEP
sqlsql-server
Lixyna
Lixyna

Reputation: 25

SQL Server: Replace all instances of a string across all columns in a select statement

I have an issue where a BCP call to one of our stored procedures returns the NUL (\0) value character whenever it finds a column with an empty string as its content. This NUL cannot be processed by the caller, and I need to find a solution on either SQL script level or data insertion level to fix this.

My idea is to replace all empty strings with a proper NULL, as that is returned by BCP as an empty string. For a small table this could be done by CASE statements or similar, but my table has around 50 varchar columns with tens of thousands of rows being pulled at once, so I'm worried this would result in big performace issues.

Is there a way to manipulate an output of a select statement in a way where all occurances of a certain character/string, across all columns and rows, are replaced with another character/string?

Upvotes: 0

Views: 196

Answers (1)

Cam
Cam

Reputation: 11

Your best bet is going to be either to update the data, update your process to properly handle empty strings, or use something like NULLIF(col, '') on the selected columns.

Upvotes: 1

Related Questions