SQL Query

[RajTS]

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.CU ST_STAT_CD,
replace(replace (replace(replac e(T1.NAME,char( 10),''),char(13 ),''), char(9),''),cha r(44), '') as NAME,T1.LOC,
case when (CHARINDEX(CHAR (13)+CHAR(10), T2.ADDR) -1) < 0
then REPLACE(SUBSTRI NG(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(CHA R(13)+CHAR(10), T2.ADDR, CHARINDEX(CHAR( 13)+CHAR(10), T2.ADDR) + 1))-1) < 0
then SUBSTRING(T2.AD DR, (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(SUBST RING(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(1 0), T2.ADDR) - 2), 1, 30) end as ADDR2,--- second split replace(replace (replace(replac e(T2.COUNTY,cha r(10),''),char( 13),''), char(9),''), char(44), '') as COUNTY,
replace(replace (replace(replac e(T2.COUNTRY,ch ar(10),''),char (13),''), char(9),''), char(44), '') as COUNTRY,
replace(replace (replace(replac e(T2.CITY,char( 10),''),char(13 ),''), char(9),''), char(44), '') as CITY,
replace(replace (replace(T2.ZIP CODE,char(10),' '),char(13),'') , char(9),'') as ZIPCODE,
replace(replace (replace(T1.OU_ NUM,char(10),'' ),char(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