CODEWITHSUNDEEP
c#.netsql-server
JayJay
JayJay

Reputation: 1068

How delete in sql server a word in the whole column?

I just fill up my database with an excel file and after checking out it i saw that in many column there is an error.

I check out on internet and it is a bug of Excel when insert cells in multiline. So now the word "x000D" is present in many columns.

How i can delete it with a query with sql in the whole database?

Upvotes: 1

Views: 570

Answers (3)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

If you wanna do this on all your database, you have to create a procedure looping through

the INFORMATION_SCHEMA.COLUMNS view, which will give you all columns (with their tables) of your database. You can check the DATA_TYPE of your columns (you don't need to update integer columns, for example)

Then with dynamic sql, build a query like that in the loop

UPDATE <MYTABLE> SET <MYCOLUMN> = REPLACE(<MYCOLUMN>, 'x000D', '')

and execute it.

EDIT

Faster, not so clean, without a stored proc, you can type in SQL Server Management studio :

SELECT 'UPDATE ' 
+ c.TABLE_NAME 
+ ' SET ' 
+ c.COLUMN_NAME 
+ ' =REPLACE(' + c.COLUMN_NAME + ', ''x000D'', '''');'
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.DATA_TYPE = 'nvarchar' // or IN (<the types you wanna correct>)

you copy the result and execute it, and you're done.

Upvotes: 2

Wencesbc
Wencesbc

Reputation: 116

Could be easier to trunk and reload the tables, with the corrected xls

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726599

You can do this:

update MyTable -- <<=== Your table name
set myColumn = null -- <<=== Your column name
where myColumn='x000D'

This query will clear out the value x000D from the myColumn in all rows that are set to x000D.

Upvotes: 1

Related Questions