TSQL 2008 使用 LTrim(RTrim 并且数据中仍然有空格
在将旧数据表移动到新数据表之前，我清理了旧数据表中的数据。其中一个字段在右侧和右侧的列中有空格。左边。我编写了以下代码来解决这个问题，但仍然有前导空格？使用此代码时，大部分数据是干净的，但由于某种原因，RT 地址之前有空格......还有其他人遇到过此类问题吗？
,CASE
WHEN PropStreetAddr IS NOT NULL
THEN (CONVERT(VARCHAR(28),PropStreetAddr))
WHEN PropStreetAddr is NOT NULL Then (Select LTrim(RTrim(PropStreetAddr)) As PropStreetAddr)
ELSE NULL END as 'PROPERTY_STREET_ADDRESS'
输出数据示例：
1234 20th St
RT 1 BOX 2
560 King St
610 Nowland Rd
RT 1
1085 YouAreHere Ln
RT 24 Box 12
I have data I cleaning up in an old data table prior to moving it to a new one. One of the fields has spaces in the column, right & left. I wrote the following code to address this and still have leading spaces?? The bulk of the data is clean when using this code but for some reason there are spaces prior to RT addresses... Has anyone else had this type of issue?
,CASE
WHEN PropStreetAddr IS NOT NULL
THEN (CONVERT(VARCHAR(28),PropStreetAddr))
WHEN PropStreetAddr is NOT NULL Then (Select LTrim(RTrim(PropStreetAddr)) As PropStreetAddr)
ELSE NULL END as 'PROPERTY_STREET_ADDRESS'
Sample output data:
1234 20th St
RT 1 BOX 2
560 King St
610 Nowland Rd
RT 1
1085 YouAreHere Ln
RT 24 Box 12
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我遇到了同样的问题 - 将字符串包装在 CAST(x as varbinary(64)) 中显示十六进制，从中我看到 A000 - 我相信这是不间断的空格。
要删除，请尝试此操作（对于 UNICODE）；
I've had the same problem - wrapping the string in a CAST(x as varbinary(64)) shows the hex and from this I see A000 - which I believe is non-breaking space.
To remove, try this (for UNICODE);
使用：
Use:
这是可行的表达式。我假设没有不可见的内容。如果您怀疑的话，您仍然应该追求@OMG Ponies 的建议。而且我认为如果必须处理不可见的内容，可以在该表达式中添加 PATINDEX 表达式。
SQL Server CASE 仅处理一个 WHEN 子句然后中断。所以你永远不会进行第二次数据转换。此外，当您使用 LTRIM 和 RTRIM 函数时，所有 NULL 值都将转换为 NULL。因此，您不需要测试它，除非您想对 NULL 执行某些操作。
所以，试试这个：
Here's the expression that will work. I'm assuming there is no non-visible content. You should still pursue @OMG Ponies recommendation if you suspect it. And I think the PATINDEX expression can be added to this expression if you must deal with the non-visible content.
SQL Server CASE processes only one WHEN clause then breaks. So you are never getting to the second data conversion. Also, all NULL values will convert to NULL when you use the LTRIM and RTRIM functions. So, you don't need to test for it, unless you want to do something with the NULLs.
So, try this:
我也有类似的情况，最初我以为是LTRIM & RTRIM 功能无法正常工作。然而，一旦我测试了它，发现它不是一个正确的空白字符（实际字符可能与您的违规不可打印字符不同），使用：
我发现该字符被命名为 160，所以我做了一个替换喜欢：
希望对某人有帮助
I have a similar situation, initially I thought the LTRIM & RTRIM functions were not working correctly. However once I tested it out and could see that it was not a proper white space character (the actual character may be different to your offending non printable character), using:
I found the character to be named 160, so then I did a replace like:
Hope it helps somebody