SQL function is applied for each column of a certain type.

Question

I am trying to save myself from adding a lot of duplicate code to a set of SQL scripts to transfer data. I am using SQL Server.

select some_int_col, replace(a_varchar_col,'|',' ') from test_table 

It is quite simple. It will return the text to a_varchar_col with all | replaced by one space.

But I got both 12 tables and 100 varchar fields. I do not want to go through and replace around each column, because its error is subject, and I am lazy;)

Is there any way to say that you need to perform a replacement on all columns of type varchar?

A more general way to ask this question:

Is there a way to get SQL Server to automatically run some arbitrary code for every column returned in the select statement? If so, can you filter which columns apply some function?

Answer 1

This is for the show and tell ONLY, and my advice is not to use it if it is absolutely necessary:

  DECLARE @tableName VARCHAR(100) SET @tableName = 'interiors' DECLARE @columnName VARCHAR(100) DECLARE columnNamesCursor CURSOR FOR SELECT c.name AS column_name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE t.name=@tableName AND SCHEMA_NAME(schema_id) = 'dbo' AND c.system_type_id = 167 OPEN columnNamesCursor fetch next from columnNamesCursor into @columnName while @@fetch_status = 0 BEGIN DECLARE @sql NVARCHAR(MAX) SET @sql = '' SET @sql = 'UPDATE ' + @tablename + ' SET ' + @columnName + ' = ' + 'replace(' +@columnName +',''|'','' '' '+ ')' EXEC sp_executesql @sql fetch next from columnNamesCursor into @columnName END CLOSE columnNamesCursor 

Answer 2

This is probably not the best solution, but it can speed up the creation of the script. You can simply copy the insertion of column names into your scripts.

  SELECT Case When systypes.name = 'varchar' then 'replace(' + sysColumns.name + ',' + '''' + 'Replace' + '''' + ', ' + '''' + 'WithThis''' + '),' else sysColumns.Name + ',' End, -- column_name = syscolumns.name, datatype=systypes.name FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id JOIN systypes ON syscolumns.xtype=systypes.xtype WHERE sysobjects.xtype='U' and systypes.Name <> 'sysname' and sysobjects.name = 'YourTableName' ORDER BY sysobjects.name,syscolumns.colid 

Answer 3

I have a parsing function that I always pass through sql. If he sees that db is an oracle, he replaces "+" with "|" eg.

But I port all my db calls to classes that handle this plumbing and other things like error handling and logging. Therefore, I can write SQL, pass it to my GetDataSet method, for example, and never worry about Oracle not liking the "+".

Cheers, Daniel