I need to convert the address field data in to 3 parts(ADDR1, ADDR2, ADDR3), currently the address field is split into 2 parts.
(ADDR1 and ADDR2)
code:
(select T1.ROW_ID,T1.CUST_STAT_CD,
replace(replace(replace(replace(T1.NAME,char(10),' '),char(13),''), char(9),''),char(44), '') as NAME,T1.LOC,
case when (CHARINDEX(CHAR(13)+CHAR(10), T2.ADDR) -1) < 0
then REPLACE(SUBSTRING(ADDR, 1, 30),CHAR(44),'')
else SUBSTRING(LEFT(T2.ADDR, CHARINDEX(CHAR(13)+CHAR(10), T2.ADDR) -1), 1, 30) end as ADDR1, - one split
case when (CHARINDEX(CHAR(13)+CHAR(10), T2.ADDR) -1) > 0 and ((CHARINDEX(CHAR(13)+CHAR(10), T2.ADDR, CHARINDEX(CHAR(13)+CHAR(10), T2.ADDR) + 1))-1) < 0
then SUBSTRING(T2.ADDR, (CHARINDEX(CHAR(13)+CHAR(10), T2.ADDR)+2), 30)
when (CHARINDEX(CHAR(13)+CHAR(10), T2.ADDR) -1) > 0 and (CHARINDEX(CHAR(13)+CHAR(10), T2.ADDR, CHARINDEX(CHAR(13)+CHAR(10), T2.ADDR) + 1)) > 0
then SUBSTRING(SUBSTRING(T2.ADDR, CHARINDEX(CHAR(13)+CHAR(10), T2.ADDR) + 2, CHARINDEX(CHAR(13)+CHAR(10), T2.ADDR, CHARINDEX(CHAR(13)+CHAR(10), T2.ADDR) + 1)- CHARINDEX(
CHAR(13)+CHAR(10), T2.ADDR) - 2), 1, 30) end as ADDR2,--- second split replace(replace(replace(replace(T2.COUNTY,char(10) ,''),char(13),''), char(9),''), char(44), '') as COUNTY,
replace(replace(replace(replace(T2.COUNTRY,char(10 ),''),char(13),''), char(9),''), char(44), '') as COUNTRY,
replace(replace(replace(replace(T2.CITY,char(10),' '),char(13),''), char(9),''), char(44), '') as CITY,
replace(replace(replace(T2.ZIPCODE,char(10),''),ch ar(13),''), char(9),'') as ZIPCODE,
replace(replace(replace(T1.OU_NUM,char(10),''),cha r(13),''), char(9),'') as OU_NUM,
T3.ATTRIB_38,
'' as X1, '' as X2, '' as X3, '' as X4
FROM dbo.S_ORG_EXT T1
LEFT OUTER JOIN dbo.S_ADDR_ORG T2 ON T1.PR_ADDR_ID = T2.ROW_ID
LEFT OUTER JOIN dbo.S_ORG_EXT_X T3 ON T1.ROW_ID = T3.PAR_ROW_ID
WHERE
(T1.INVSTR_FLG = 'Y'))
ORDER BY T1.NAME, T1.LOC