SQL Server Performance

need to parse a memo field? Here ya go...

Discussion in 'Contribute Your SQL Server Scripts' started by kingofSQL, Oct 6, 2004.

  1. kingofSQL New Member

    We have a SQL based ERP system that uses a memo field to store the mailing address. (gee thanks ERP developer)<br />Since our integration requires the address to go into address1 address2 and address3, we had to create a SQL view that would parse the memo field into 3 columns. Maybe you can use this code for your own parsing endeavors<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><br />Declare<br />@address varchar(8000),<br />@L1 int,<br />@N1 int,<br />@N2 int,<br />@N3 int<br />set @address = convert(varchar(8000),<br />'address line1<br />address line2<br />address line3<br />'<br />)<br /><br />set @L1 = LEN(@address)<br />set @N1 = charindex(char(13) + char(10),convert(varchar(8000),@address))<br />set @N2 = charindex(char(13) + char(10),convert(varchar(8000),@address),<br />charindex(char(13) + char(10),convert(varchar(8000),@address))+1)<br />set @N3 = charindex(char(13) + char(10),convert(varchar(8000),@address),<br />charindex(char(13) + char(10),convert(varchar(8000),@address),<br />charindex(char(13) + char(10),convert(varchar(8000),@address),charindex(char(13) + char(10),convert(varchar(8000),@address))+1)<br />)+1<br />)<br /><br />SELECT<br />@L1 length,<br />@address string,<br />@N1 CharPosition@N1, @N2 CharPosition@N2, @N3 CharPosition@N3,<br /><br />case <br />when @N1 &gt; 0<br />then left(@address,@N1-1)<br />else left(@address,@L1)<br />end as AddressLine1,<br /><br />case<br />when @N1 = 0 <br />then ' '<br /><br />when <br />@N2 &gt; 0<br />then <br />substring <br />(<br />@address,<br />@N1+2,<br />@N2-(@N1+2) <br />)<br />else <br /><br />substring <br />(<br />@address,<br />@N1+2, <br />@L1 <br />)<br />end as AddressLine2,<br /><br />case<br />when @N2 = 0 <br />then ' '<br />when @N3 &gt;= 0 <br />then <br />substring <br />(<br />@address,<br />@N2+2,<br />@L1 <br />)<br />else <br />' '<br />end as AddressLine3<br /><br />it's all good

Share This Page