SQL 是否可以在单个语句中修剪列中的所有值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7803833/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to Trim all the values in a column in a single statement?
提问by BlueChippy
I've a table in a (MS) SQL database which has an Id column (identity, int) and a Name column (varchar(250)). However, the values in the name column contain (fairly random) leading and trailing spaces as I think they were cut and pasted from "something else" (no idea what!).
我在 (MS) SQL 数据库中有一个表，它有一个 Id 列（identity，int）和一个 Name 列（varchar(250)）。但是，名称列中的值包含（相当随机的）前导和尾随空格，因为我认为它们是从“其他东西”（不知道是什么！）剪切和粘贴的。
Is it possible in T-SQL to do the following:
是否可以在 T-SQL 中执行以下操作：
update MyTable set Name = trim(name)
and have it update all the Name columns with the trimmed value?
并让它用修剪后的值更新所有名称列？
回答by Barry Jordan
MS SQL does not have a trim function. You'll need to use rTrim and lTrim together.
MS SQL 没有修剪功能。您需要同时使用 rTrim 和 lTrim。
update MyTable set Name = lTrim(rTrim(name))
回答by StuartLC
Try
尝试
update MyTable set Name = LTRIM(RTRIM((name))
回答by Marco
回答by cjk
In SQL Server there is only RTRIMand LTRIM, but you can use them both together:
在 SQL Server 中只有RTRIMand LTRIM，但您可以同时使用它们：
update MyTable set Name = RTRIM(LTRIM((name))
回答by silentgut
Try this:
尝试这个：
UPDATE [table] SET [column1] = REPLACE([column1],'i:0#.w|',' ')
UPDATE [table] SET [column1] = REPLACE([column1],'i:0#.w|',' ')
回答by Widor
Not quite - there's no TRIM()function available, so you have to use RTRIM()and LTRIM()(right- and left-trim, respectively):
不完全 - 没有TRIM()可用的功能，因此您必须使用RTRIM()和LTRIM()（分别为右侧和左侧修剪）：
UPDATE MyTable set Name = rtrim(ltrim(name))
