title: SQL Parse out multiple substrings
body: <p>I have a very long and complex string that comes in with New line breaks - I am having a hard time parsing. I need to be able to create a select query with a column for each of the below fields.</p>
  
  <p>The ideal would be find the <code>new line break</code> - for each line - go back to the <code>:</code> everything before the colon should be the name of the column, and everything between <code>:</code> and <code>new ling break</code> should be the data in the field. </p>
  
  <p>All the data is returned as strings so i'm just building a select statement for each of the below lines. I'm not sure if that is at all possible. </p>
  
  <p>The second alternative, to hard code and say something like <code>CHARINDEX ( 'Home Phone:' ,notes, 0)</code> Where I find the home phone string and then pull everything between the <code>:</code> and the <code>new ling break</code> after specifying the string. </p>
  
  <p><em>In this case each select item in my query will say - find string "Home Phone" and pull what comes after the colon, or find string "School Name" etc.</em> </p>
  
  <p>This is what the data looks like (in one all string called <code>notes</code>):</p>
  
  <pre><code>Home Phone: 1234567890  
  Cell Phone: 1234567890  
  Date of Birth: 01/01/1971 
  School Name: James Jones High  School 
  Address:123 Main Street 
  School City: Queens  
  School State: PA  
  School Zip: 32112 
  Years Teaching: 12  
  Grade Levels: Middle School  
  Total Students: 120  
  Subject: Music:   
  How did they hear:  Other, provide more info: Former partner teacher in the Middle School 
  Type: Public/Charter   
  Question 1: aaaaaaaa aaaaaaaaaaaaaaaaa aaaaaaaaaaaaaa aaaaaaaaaaaaaa aaaaaaaaa aaaaaaa aaaa aaa aaaaaaaa aaaaaa aaaaaaaa  aaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaa aaaaaaa aaaaaaaa aaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaa aaaaaa aaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaa aaaaaaaa aaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaa aaaaaaaaaaaaaaa aaaaaaaaaaa aaaaaaaa aaaaaaaaaaaaa aaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaa aaaaaa aaaaaa aaaaaaa aaaaaaaa aaaaaaaaaaaaaa aaaaaaaaaaa aaaaa aaaaaa aaaaaa aaaaaaaaaaaa aaaaaaaaaaaa aaa aaaa aaaaa aaaaaaaaaa aaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaa aaaaa aaaaaaaaaa aaaaaaaaaa aaaaaaaaaaa aaaaaaaaaaa aaaaaaaaa aaaaaaaaaaaa.   
  Question 2: bbb bbbbbbbbbb bbbbbbbbbbbbbbbbb bbbbbbbbbbbbbbbbbbb bbbbbbbbbbbb bbbbbbb bbb bbbbbbbbbb bbbbbbbbbbbbbbbbb bbbbbbbbbbbbbbbbbbb bbb bbbbbbbbb bbbbbbb bbbbbb bbbbbb bbbbbbb  bbb bbbbbbbbbb bbbbbbbbbbbbbbbbb bbbbbbbbbbbbbbbbbbb bbbbbbbbbbbb bbbbbbb bbbbbbbbbbbb bbbbbbb bbbbbbbbbb bbbbbbbbbbbbbbbbb bbbbbbbbbbbbbbbbbbb bbbbbbbbbbbb bbbbbbbbbbbb bbbbbbb bbb bbbbbbbbbb bbbbbbbbbbbbbbbbb bbbbbbbbbbbbbb 
  Question 3: ccccccccccccccccccccccc cccccccc ccccccccccc cccccccccccccccccccccc ccc ccccccccc cccccccccccccc ccccccccccccccccccccc cccccccccccccccccccccc cccccccccccccccccc ccccccccccc ccccccccccccc ccccccccccccccccc cccccccc
  </code></pre>
  
  <p>so the output looks something like this (with all of the long questions answered as well in each field). </p>
  
  <pre><code>Home Phone  Cell Phone  Date of Birth:  …   Type:               Question 1 :                Question 2:    Question 3: 
  1234567890  1234567890  1/1/1971            Public/Charter      aaaaaaaa aaaaaaaaaaaaa.     bbb bbbbbbbbbb ccccccccccccccccccccccc 
  </code></pre>
  
  <p>I'm not sure if that makes sense -- but any and all suggestions are really appreciated. </p>
  
  <p>Code to pull the substring and the new line char -- but this is hard coded. I can't figure out how to do it dynamically. </p>
  
  <pre><code>SELECT  ltrim(rtrim(CHARINDEX ( 'Home Phone:' ,notes, 0) + LEN('Home Phone: '))) as 'beggining',
          ltrim(rtrim(CHARINDEX ( CHAR(10) ,notes, 0)))   as 'ending',
          SUBSTRING(notes,(CHARINDEX ( 'Home Phone:' ,notes, 0) + LEN('Home Phone: ')),(LEN('Home Phone: '))) as 'home phone',    
  FROM    table a 
  </code></pre>
  
  <p>Thank you! </p>