SELECT REPLACE per column
3 answers
DECLARE @tablename nvarchar(100)= 'Test'
DECLARE @col nvarchar(max)
SELECT @col = coalesce(@col + ',', 'select ' ) +
case when data_type in ('varchar', 'char','nvarchar', 'nchar') then
'replace('+column_name+' , ''"'', '''') '+' as [' + column_name + ']' else '[' + column_name + ']' end
FROM INFORMATION_SCHEMA.COLUMNS a
WHERE table_name = @tablename
SET @col += ' from ' + @tablename
EXEC (@col)
+4
source to share
Since you are using SQL Server, you can get the names of all columns in a table using INFORMATION_SCHEMA, for example
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'yourTable'
Then you can use a cursor to iterate over each column name, build some dynamic SQL and execute it with "exec sp_executesql".
Here's my solution:
declare @isString bit
declare @tableName nvarchar(256) = N'MyTableName'
declare @columnName nvarchar(max)
declare @sql nvarchar(max) = ''
declare c cursor local forward_only read_only for
select column_name, case when CHARACTER_SET_NAME is null then 0 else 1 end as IsString
from information_schema.COLUMNS WHERE table_name = @tableName
open c
fetch next from c into @columnName, @isString
set @sql = N'select '
declare @first bit = 1
while @@FETCH_STATUS = 0
begin
select @columnName
if @isString <> 0
begin
if @first = 0
begin
set @sql = @sql + ', '
end
set @sql = @sql + N'REPLACE(' + @columnName + ', ''"'', '''')'
set @first = 0
end
fetch next from c into @columnName, @isString
end
close c
deallocate c
set @sql = @sql + ' from ' + @tableName
exec sp_executesql @sql
+1
source to share
Here's a recursive version:
declare @TABLE_NAME sysname = 'MyTableName'
declare @Prefix nvarchar(128) = 'REPLACE('
declare @Suffix nvarchar(128) = ', ''"'', '''')'
declare @Sql nvarchar(max)
;with Cols (TABLE_NAME, SELECT_LIST, ITERATION) as
(
select TABLE_NAME
, cast('' as nvarchar(max)) as SELECT_LIST
, 0 as ITERATION
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME = @TABLE_NAME
union all
select c.TABLE_NAME
, c.SELECT_LIST
+ case when len(c.SELECT_LIST) > 0 then ', ' else '' end
+ case when i.DATA_TYPE like '%char' then @Prefix else '' end
+ cast(i.COLUMN_NAME as nvarchar(128))
+ case when i.DATA_TYPE like '%char' then @Suffix + ' as ' + cast(i.COLUMN_NAME as nvarchar(128)) else '' end
, c.ITERATION + 1
from INFORMATION_SCHEMA.COLUMNS i
join Cols c on i.TABLE_NAME = c.TABLE_NAME
where i.ORDINAL_POSITION = c.ITERATION + 1
)
select @Sql = ('select ' + a.SELECT_LIST + ' from ' + a.TABLE_NAME)
from Cols a
join (
select TABLE_NAME, max(ITERATION) as ITERATION
from Cols
group by TABLE_NAME
) as b on a.TABLE_NAME = b.TABLE_NAME
and a.ITERATION = b.ITERATION
exec (@sql)
0
source to share