title: Splitting one irregular column into many columns
body: <p>I need to split one irregular column into many subsidiary columns using various delimiters SQL Server. </p>
  
  <p>I have a column called Event_Name that has the following irregular data:</p>
  
  <pre class="lang-py prettyprint-override"><code>EVENT_NAME
  ----------------
  ABBRV
  Noun Noun2 Noun3 - Adjective - MM/DD/YYYY - LOCATION
  Noun Noun2 - MM/DD/YYYY (#1) - LOCATION 
  Noun Noun2 - MM/DD/YYYY - Adjective (#1) - LOCATION
  Noun, Noun1a Noun2 Noun3 - Adjective: MM/DD/YYYY - Adjective2 - LOCATION
  
  like:
  "QRCC"
  "Pool Party Dance - Late Night - 12/12/2020 - North"
  "Lawn Bowling - 12/12/2020 (#1) - South "
  "Lawn, Pool Class Signups - Early: 12/12/2020 - Canceled - North" 
  "Pool Event - 11/31/2020 - To Be Announced (#1) - South"
  </code></pre>
  
  <p>I previous tried to solve this using Python but, now, the way the pipeline in structured, I really need to do the split in the SQL query and use the embedded date as a condition. </p>
  
  <p>In Python, I was working on something like</p>
  
  <pre class="lang-py prettyprint-override"><code>new= df['Event_Name'].str.split(" ",n=2, expand = True)
  new[3] = new[2].str.split("-", expand= True)[1]
  new[4] = new[2].str.split("-", expand= True)[2]
  new[5] = new[2].str.split("-", expand= True)[3]
  
  new[3] = new[3].str.split(' ()', expand=True)[2]
  
  new[5]=new[2].str[-4:]
  data = new[[0,1,3,5]]
  
  0    |  1    | 3          |  5
  -------------------------------
  Noun | Noun2 | xx/xx/xxxx |  LOCATION
  
  
  </code></pre>
  
  <p>but that doesn't account for the Adjectives and doesn't capture that in the rare occasions that there's a <code>"Noun, Noun1a Noun2 Noun3 - Adjective: MM/DD/YYYY - Adjective - LOCATION"</code>  format, the output should be</p>
  
  <pre class="lang-py prettyprint-override"><code>0            |  1   | 3          |  5
  ----------------------------------------------
  Noun, Noun1a | Noun2|  xx/xx/xxxx|  LOCATION
  </code></pre>
  
  <p>so the actual desired output would be </p>
  
  <pre class="lang-py prettyprint-override"><code>(Noun and Noun1a if not null) or ABBRV | Noun2 or null | Noun3 or null | DATE | Adjective or null| Adjective 2 or null| LOCATION  
  
  OR
  
  Event Cat | Detail | Detail | DATE       | Status     | Status  | LOCATION
  -------------------------------------------------------------
  QRCC
  Pool      | Party   | Dance  | 12/12/2020 | Late Night |         | North
  Lawn      | Bowling |        | 12/12/2020 |            |         | South 
  Lawn, Pool| Class   | Signups| 12/12/2020 | Early      |Canceled |North 
  Pool      |Event    |        |11/31/2020  | To Be Announced |    | South 
  </code></pre>
  
  <p>The occasional <code>"(#1)"</code> is irrelevant and can be ommited. How can I do this as part of a SQL call?</p>