TSQL 2008 使用 LTrim(RTrim 并且数据中仍有空格

2022-01-12 00:00:00 formatting sql-server trim

在将旧数据表移动到新数据表之前，我在清理旧数据表中的数据.其中一个字段在列中有空格，right &左边.我写了下面的代码来解决这个问题并且仍然有前导空格?使用此代码时，大部分数据是干净的，但由于某种原因，在 RT 地址之前有空格......还有其他人遇到过这种类型的问题吗?

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'

样本输出数据:

1234 20th St 
  RT 1 BOX 2  
560 King St  
610 Nowland Rd  
  RT 1  
1085 YouAreHere Ln  
  RT 24 Box 12

推荐答案

下面是可行的表达式.我假设没有不可见的内容.如果您怀疑，您仍然应该寻求@OMG Ponies 的推荐.而且我认为如果必须处理不可见的内容，可以将 PATINDEX 表达式添加到此表达式中.

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 只处理一个 WHEN 子句然后中断.因此，您永远不会进行第二次数据转换.此外，当您使用 LTRIM 和 RTRIM 函数时，所有 NULL 值都将转换为 NULL.因此，您不需要对其进行测试，除非您想对 NULL 做一些事情.

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.

那么，试试这个:

CONVERT(VARCHAR(28), LTRIM(RTRIM(PropStreetAddr))) as [PROPERTY_STREET_ADDRESS]

相关文章