如何在sql server中删除整列中的一个单词？

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.

我只是用excel文件填满我的数据库,在检查后我看到很多列中都有错误。

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.

我在互联网上查看,当在多行中插入单元格时,它是Excel的错误。所以现在许多专栏中都有“x000D”这个词。

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

如何在整个数据库中使用sql查询删除它?

3 个解决方案

#1

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)

INFORMATION_SCHEMA.COLUMNS视图,它将为您提供数据库的所有列(及其表)。您可以检查列的DATA_TYPE(例如,您不需要更新整数列)

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

然后使用动态sql,在循环中构建一个类似的查询

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 :

更快,不那么干净,没有存储过程,您可以输入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.

你复制结果并执行它,你就完成了。

#2

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.

此查询将在设置为x000D的所有行中从myColumn中清除值x000D。

#3

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

使用更正的xls可以更容易中继和重新加载表

#1