CODEWITHSUNDEEP
sql-server
Sudeep Hazra
Sudeep Hazra

Reputation: 128

SQL Server - Replace() in a select * query

I have a procedure that does a "select * from view" and this output contains an inverted comma in one of the columns. I want to do a replace() to remove the extra inverted comma but I cannot change the "select *" as this is a dynamic query that is used in different scenarios.

As evident, the following query does not work

select replace(*, '"', '') from ReportRegistry

Some help appreciated.

I wanted to know if there is any way I can remove the quotes without knowing the column names. I want them to be removed from all columns. The columns names are different in different cases.

The view is defined within the procedure differently in different cases. The dynamic colum names are added as text and them executed using sp_executesql.

Upvotes: 1

Views: 2755

Answers (3)

Jay
Jay

Reputation: 1

declare @tbl varchar(max) = 'table_or_view_name'

declare @cols varchar(max) =
    (select case when data_type='varchar' then 'REPLACE('+column_name+',''"'','''')' else column_name end+char(10)+','
    from information_schema.columns
    where table_name = @tbl for xml path(''))

declare @sql varchar(max)='select '+left(@cols,len(@cols)-1)+' from '+@tbl

print @sql
exec(@sql)

Upvotes: 0

Dave Mason
Dave Mason

Reputation: 4936

DECLARE @ColNames NVARCHAR(MAX) = ''

--Dynamically build a list of column names for the view, separated by commas.
SELECT @ColNames = @ColNames + 
    CASE
        --Use the REPLACE function for "String" type data types.  Did I leave any data types out?
        --Change "x" to the character(s) you want to replace.
        WHEN c.DATA_TYPE IN ('VARCHAR', 'CHAR', 'NVARCHAR', 'NCHAR') THEN 'REPLACE([' + c.COLUMN_NAME + '], ''x'', '''') AS [' + c.COLUMN_NAME + '],'
        --All other data types.
        ELSE '[' + c.COLUMN_NAME + '],'
    END
--In addition to user tables, views are included in INFORMATION_SCHEMA.COLUMNS (and in INFORMATION_SCHEMA.TABLES).
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = 'ReportRegistry'
--Retain original order of the view's columns.
ORDER BY c.ORDINAL_POSITION

--Remove last comma
SET @ColNames = LEFT(@ColNames, LEN(@ColNames) - 1)

EXEC ('SELECT ' + @ColNames + ' FROM ReportRegistry ')

Upvotes: 1

M.Ali
M.Ali

Reputation: 69494

select replace(TargetColumnName, '"', '')
       ,Column2
       ,Column3
       ,..... and so on..... 
from ReportRegistry

Upvotes: 0

Related Questions