CODEWITHSUNDEEP
sql-serverssis
user2532318
user2532318

Reputation: 13

How to remove special charectors in a table dynamically

Hi I want to remove special characters from my table i am trying with a stored proc can any of you guys suggest a better way of approach. i want to remove only certain special characters like ?,',",# and still my date have $ or some other symbols.

     CREATE PROCEDURE [dbo].[ScrubData]
      (
       @TableName Varchar(100)
         )
        AS
        BEGIN

         DECLARE @SQL1 nVARCHAR(MAX)
         DECLARE @SQL2 nVARCHAR(MAX)
         DECLARE @SQL3 nVARCHAR(MAX)
         DECLARE @SQL4 nVARCHAR(MAX)

          select @SQL1 =
          'update ' + TABLE_NAME + 
             '  set ' + column_name + ' = replace ('+ column_name +', '','','''')
            where ' + column_name + ' like ''%,%'''
          from INFORMATION_SCHEMA.COLUMNS
           where TABLE_NAME = @TableName and COLUMN_NAME like '%load'

           select @sql2 =
           'update ' + TABLE_NAME + 
         '  set ' + column_name + ' = replace ('+ column_name +', '''''','''')
            where ' + column_name + ' like ''%''%'''
            from INFORMATION_SCHEMA.COLUMNS

         where TABLE_NAME = @TableName and COLUMN_NAME like '%load'

      select @SQL3 =
      'update ' + TABLE_NAME + 
      '  set ' + column_name + ' = replace ('+ column_name +', ''"'','''')
      where ' + column_name + ' like ''%"%'''
      from INFORMATION_SCHEMA.COLUMNS
      where TABLE_NAME = @TableName and COLUMN_NAME like '%load'

       select @SQL4 =
       'update ' + TABLE_NAME + 
        '  set ' + column_name + ' = replace ('+ column_name +', ''#'','''')
        where ' + column_name + ' like ''%#%'''
       from INFORMATION_SCHEMA.COLUMNS
         where TABLE_NAME = @TableName and COLUMN_NAME like '%load'


     print(@sql1)
        print(@sql2)
      print(@sql3)
       print(@sql4)

      end

Upvotes: 1

Views: 64

Answers (1)

Rahul Tripathi
Rahul Tripathi

Reputation: 172428

Try something like this:-

SELECT REPLACE( REPLACE( REPLACE( REPLACE( @str, '!', '' ), '#', '' ), '$', '' ), '&', '' );

Upvotes: 1

Related Questions