title: How to break down a string using CR LF characters?
body: <p>I have an address string that I need to break down to 4 fields <code>Add1</code>, <code>Add2</code>…</p>
  
  <p>I need to use <code>CHAR(13)+CHAR(10)</code> as the delimiter. The string looks like <code>text1CRLFtext2CRLFtext3</code>. I should have the following:</p>
  
  <pre><code>add1 = text1 (SUBSTRING(field, 0, CHARINDEX(CHAR(13)+CHAR(10),field))
  add2 = text2
  add3 = text3
  </code></pre>
  
  <p>But I am having issues populating the remaining fields (how to calculate the length between the CRLFs).</p>
  
  <p>This is on MS SQL server.</p>
  
  <p>here is what I came up with</p>
  
  <pre><code>DECLARE @document VARCHAR(64);
  SELECT @document = 'we
  want
  the
  world
  now';
  
  SELECT addr1 = SUBSTRING(@document, 1, CHARINDEX(CHAR(13)+CHAR(10),@document)-1)
  SELECT addr2 = SUBSTRING(@document, CHARINDEX(CHAR(13)+CHAR(10),@document)+2, CHARINDEX(CHAR(13)+CHAR(10),@document,CHARINDEX(CHAR(13)+CHAR(10),@document)+2)-1-(CHARINDEX(CHAR(13)+CHAR(10),@document)+1))
  SELECT addr3 = SUBSTRING(@document, CHARINDEX(CHAR(13)+CHAR(10),@document,CHARINDEX(CHAR(13)+CHAR(10),@document)+2)+2, CHARINDEX(CHAR(13)+CHAR(10),@document,CHARINDEX(CHAR(13)+CHAR(10),@document,CHARINDEX(CHAR(13)+CHAR(10),@document)+2)+2)-1-(CHARINDEX(CHAR(13)+CHAR(10),@document,CHARINDEX(CHAR(13)+CHAR(10),@document)+2)+2-1))
  SELECT addr4 = SUBSTRING(@document, CHARINDEX(CHAR(13)+CHAR(10),@document,CHARINDEX(CHAR(13)+CHAR(10),@document,CHARINDEX(CHAR(13)+CHAR(10),@document)+2)+2)+2, CHARINDEX(CHAR(13)+CHAR(10),@document,CHARINDEX(CHAR(13)+CHAR(10),@document,CHARINDEX(CHAR(13)+CHAR(10),@document,CHARINDEX(CHAR(13)+CHAR(10),@document)+2)+2)+2)-1-(CHARINDEX(CHAR(13)+CHAR(10),@document,CHARINDEX(CHAR(13)+CHAR(10),@document,CHARINDEX(CHAR(13)+CHAR(10),@document)+2)+2)+2-1))
  SELECT addr5 = SUBSTRING(@document, CHARINDEX(CHAR(13)+CHAR(10),@document,CHARINDEX(CHAR(13)+CHAR(10),@document,CHARINDEX(CHAR(13)+CHAR(10),@document,CHARINDEX(CHAR(13)+CHAR(10),@document)+2)+2)+2)+2, LEN(@document)-CHARINDEX(CHAR(13)+CHAR(10),@document,CHARINDEX(CHAR(13)+CHAR(10),@document,CHARINDEX(CHAR(13)+CHAR(10),@document,CHARINDEX(CHAR(13)+CHAR(10),@document)+2)+2)+2)-1)
  </code></pre>
  
  <p>Now I need to turn this into a view that handles four different scenarios – when the string has only one CRLF, when it has two or more CRLF, up to 4.</p>