TSQL 2008使用LTrim(RTrim并且在数据中仍然有空格 [英] TSQL 2008 Using LTrim(RTrim and still have spaces in the data
问题描述
$ B当propStreetAddr不为NULL
THEN(CONVERT(VARCHAR(28),PropStreetAddr) )
当PropStreetAddr不是NULL然后(选择LTrim(RTrim(PropStreetAddr))作为PropStreetAddr)
ELSE NULL END as'PROPERTY_STREET_ADDRESS'
$ p $
$ b $方框2
560 King St
610 Nowland Rd
RT 1
1085 YouAreHere Ln
RT 24方框12
这是可以工作的表达式。我假设没有不可见的内容。如果您怀疑它,您仍然应该追求@OMG小马推荐。我认为如果您必须处理不可见的内容,可以将PATINDEX表达式添加到此表达式中。
SQL Server CASE只处理一个WHEN子句,然后中断。所以你永远不会进行第二次数据转换。此外,当您使用LTRIM和RTRIM函数时,所有NULL值将转换为NULL。所以,你不需要测试它,除非你想用NULL做一些事情。
所以,试试这个:作为[PROPERTY_STREET_ADDRESS]
$ p的
CONVERT(VARCHAR(28),LTRIM(RTRIM(PropStreetAddr))) $ p>
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
解决方案 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:
CONVERT(VARCHAR(28), LTRIM(RTRIM(PropStreetAddr))) as [PROPERTY_STREET_ADDRESS]
这篇关于TSQL 2008使用LTrim(RTrim并且在数据中仍然有空格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!