所以我有一个选择
select
PART_NO,
FIPS_COUNTY_CODE as COUNTY_CODE,
LAST AS LNAME,
FIRST AS FNAME,
IsNull(ALTADDR, '') + ' ' + DELADDR as ADDRESS,
CITY,
STATE,
ZIP,
SEX,
RACE,
STATUS,
convert(datetime, BIRTHDATE) as DOB,
H_PHONE,
VOTER_DIV,
VOTER_REG,
JUD_CODE,
[LICENSE NO] AS D_LICENSE,
SIN,
DATE_SELECTED1,
DATE_SELECTED2,
DATE_SELECTED3,
PART_NO - 100000000 AS REC_NUM,
PERM_DISQUAL,
SCAN_CODE,
DIVISION AS DIVISION_CODE,
OFFICIAL_USE,
COURT_EMP,
HISPANIC,
NOTES,
CITIZEN,
DATE_UPD,
DS as SOURCE,
CELL_PHONE,
W_PHONE,
W_PH_LOCAL,
EMAIL,
PENDING_IND,
PENDING_EXPIRES,
LOGIN_LOCK,
DATE_SELECTED4,
QUALIFIED_DATE,
POOL_CREATION_DATE
into FINAL
INTO FINAL
FROM NCOASUPPRESS
ALTADDR 和 DELADDR 需要组合,例如:
ALTADDR = APT B1
DELADDR = 1000 Goggins Ln
预期结果 = APT B1 1000 戈金斯 Ln
但有时 ALTADDR 是空白的,所以发生的情况是在预期结果之前放入一个额外的空格。还之后...?但它不会对任何其他领域执行此操作。我尝试了LTRIM/RTRIM,但它们似乎不起作用。我基本上需要为引线和尾随空格修剪此字段,但修剪功能似乎对我不起作用。
处理COALESCE()
内的空间:
COALESCE(ALTADDR + ' ', '') + DELADDR as ADDRESS,
由于您已经尝试过ltrim()
和rtrim()
,因此数据中可能有额外的空格字符,这些空格字符不是实际的空格字符。
要替换制表符、新行和回车符...尝试:
address = replace(replace(replace(isnull(altaddr + ' ', '') + deladdr
,char(10),'') /* new line */
,char(13),'') /* carriage return */
,char(9),'') /* tab */
如果仍然看到空格,请将上述内容括起来 ltrim(rtrim(replace...))
.
这个版本将有助于从开头剪掉空格,但不能从结尾剪掉
: address = substring((isnull(altaddr + ' ', '') + deladdr)
, patindex('%[a-z0-9]%',(isnull(altaddr + ' ', '') + deladdr))
, 8000)
超级修剪
SELECT LTRIM(RTRIM(CONCAT(' ' + LTRIM(RTRIM(ALTADDR)), ' ' + LTRIM(RTRIM(DELADDR)))))
尝试使用 CONCAT
CONCAT(ALTADDR + ' ', DELADDR) as ADDRESS
这将处理ALTADDR
和DELADDR
上的NULL
编辑
OTT 方法:CONCAT(LTRIM(RTRIM(ALTADDR)) + ' ', LTRIM(RTRIM(DELADDR))) as ADDRESS
也许你用错了L/RTRIM()
....
declare @ALTADDR varchar(64) = ' '
declare @DELADDR varchar(64) = '1000 Goggins Ln'
SELECT LTRIM(RTRIM(CONCAT(RTRIM(@ALTADDR) + ' ',LTRIM(@DELADDR)))) as ADDRESS
这可确保无论字段是' '
还是NULL
,您都没有前导空格或尾随空格