Remove characters from a SQL Table [closed]

Question

Closed. This question needs to be more focused. It is not currently accepting answers.

Want to improve this question? Guide the asker to update the question so it focuses on a single, specific problem. Narrowing the question will help others answer the question concisely. You may edit the question if you feel you can improve it yourself. If edited, the question will be reviewed and might be reopened.

Closed 11 years ago.

Improve this question

I have around 80 table (each table has around 12,000 rows and 190 columns)

I need to clean up the data by removing a few characters in it. (ƒ, € and ¢)

These characters appear throughout the entire database (can be anywhere in the data) and I need to remove all of them. Is there any efficient way to do so?

I have tried writing a C# application - using datareader to read the data, then check for occurrence of the characters then remove them, and finally update the data back to the table. However, such method is too time consuming.

Nathan Bedford · Accepted Answer · 2014-04-07 16:07:32Z

I'm sure there are more efficient methods, but something like this should work for you.

I'm using the system tables/views to get the lists of databases and columns, then using dynamic SQL to build the update statement. As a one-off process, this should be fine.

------------------------------------------------------------------------------
-- Define find/replace values here
------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#FindAndReplace') IS NOT NULL DROP TABLE #FindAndReplace
CREATE TABLE dbo.#FindAndReplace(
    FindValue VARCHAR(255) PRIMARY KEY,
    ReplaceValue VARCHAR(255)
)

INSERT INTO #FindAndReplace SELECT 'ƒ', 'replacement'
INSERT INTO #FindAndReplace SELECT '€', 'goes'
INSERT INTO #FindAndReplace SELECT '¢', 'here'


------------------------------------------------------------------------------
-- Then build and execute replace statements here
------------------------------------------------------------------------------
--loop through all tables
DECLARE @TableName VARCHAR(255)
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

OPEN TableCursor
FETCH NEXT FROM TableCursor 
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

    --then all columns
    DECLARE @ColumnName VARCHAR(255)
    DECLARE ColumnCursor CURSOR FOR
    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TableName
    AND DATA_TYPE IN ('nvarchar', 'varchar')

    OPEN ColumnCursor
    FETCH NEXT FROM ColumnCursor 
    INTO @ColumnName
    WHILE @@FETCH_STATUS = 0
    BEGIN

        --then all find-replace values
        DECLARE @FindValue VARCHAR(255)
        DECLARE @ReplaceValue VARCHAR(255)
        DECLARE FindReplaceCusor CURSOR FOR
        SELECT FindValue, ReplaceValue
        FROM #FindAndReplace

        OPEN FindReplaceCusor
        FETCH NEXT FROM FindReplaceCusor 
        INTO @FindValue, @ReplaceValue
        WHILE @@FETCH_STATUS = 0
        BEGIN

            DECLARE @SQL NVARCHAR(MAX)
            SET @SQL = 'UPDATE [' + @TableName + '] SET [' + @ColumnName + '] = REPLACE([' + @ColumnName + '], ''' + @FindValue + ''', ''' + @ReplaceValue + ''') WHERE [' + @ColumnName + '] LIKE ''%' + @FindValue + '%'''
            PRINT @SQL
            EXEC sp_executesql @SQL

        FETCH NEXT FROM FindReplaceCusor INTO @FindValue, @ReplaceValue
        END
        CLOSE FindReplaceCusor
        DEALLOCATE FindReplaceCusor

    FETCH NEXT FROM ColumnCursor INTO @ColumnName
    END
    CLOSE ColumnCursor
    DEALLOCATE ColumnCursor

FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor

Community · Accepted Answer · 2017-05-23 12:20:53Z

1

You could call some T-SQL to perform the search and replace

See this answer from Search and replace part of string in database

UPDATE
    Table
SET
    Column = Replace(Column, 'find value', 'replacement value')
WHERE
    xxx

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Apr 7, 2014 at 15:17

Mike

2,5876 gold badges38 silver badges76 bronze badges

2 Comments

C.J. Over a year ago

can you please explain a little further? What do I need at the WHERE clause? Do I actually have to specify all of the column name? Please be reminded that I have around 80 tables, each of them consists of ~190 different column names

Mike Over a year ago

There is no need to specify the WHERE clause - this would only be required if you wanted to run the SET against a subset of rows - if you want to run it against the entire table, simply omit the WHERE clause - w3schools.com/sql/sql_update.asp

Collectives™ on Stack Overflow

Remove characters from a SQL Table [closed]

2 Answers 2

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Linked

Related