删除整数之前的字符

Question

pe.fdaddress在数字前有奇怪的字符。这会阻止我的代码正确加入。有没有办法删除这些字符以正确运行我的代码？因此，地址结果为空。谢谢

SELECT ca.fdorgunit AS Facility
, pt.fdmedrecnum AS Account
, ca.fddos AS DOS
, pe.fddob AS DOB
, pe.fdssn AS SSN
, ad.fdaddr1 AS [Address]
FROM OPENQUERY (VISION, 'SELECT * FROM ci.tbcase') AS ca
LEFT JOIN OPENQUERY (VISION, 'SELECT * FROM de.tbpatient') AS pt
ON pt.id = ca.fdpatient
LEFT JOIN OPENQUERY (VISION, 'SELECT * FROM de.tbperson') AS pe
ON pt.fdperson = pe.fdid
LEFT JOIN OPENQUERY (VISION, 'SELECT * FROM de.tbaddress') AS ad
ON ad.id = pe.fdaddress
WHERE ca.fdcasestatus = 'Performed'
GROUP BY ca.fdorgunit, ca.fddos, pt.fdmedrecnum, pe.fdssn, pe.fddob, ad.fdaddr1

Answer 1

双重发布并不好 - 你应该澄清原始问题。

随便...以下是避免该问题的方法。查看对查询的更改。另外，我不知道你为什么在这里使用OPENQUERY。你应该研究LINKED SERVERS

SELECT ca.fdorgunit AS Facility
, pt.fdmedrecnum AS Account
, ca.fddos AS DOS
, pe.fddob AS DOB
, pe.fdssn AS SSN
, ad.fdaddr1 AS [Address]
FROM OPENQUERY (VISION, 'SELECT * FROM ci.tbcase') AS ca
LEFT JOIN OPENQUERY (VISION, 'SELECT * FROM de.tbpatient') AS pt
ON pt.id = ca.fdpatient
LEFT JOIN OPENQUERY (VISION, 'SELECT * FROM de.tbperson') AS pe
ON pt.fdperson = pe.fdid
LEFT JOIN OPENQUERY (VISION, 'SELECT * FROM de.tbaddress') AS ad
--here is the change
ON ad.id = LEFT(SUBSTRING(pe.fdaddress, PATINDEX('%[0-9.-]%', pe.fdaddress), 8000),PATINDEX('%[^0-9.-]%', SUBSTRING(pe.fdaddress, PATINDEX('%[0-9.-]%', pe.fdaddress), 8000) + 'X') -1)
WHERE ca.fdcasestatus = 'Performed'
GROUP BY ca.fdorgunit, ca.fddos, pt.fdmedrecnum, pe.fdssn, pe.fddob, ad.fdaddr1