title: SQL Parse Duration String to Seconds
body: <p>I am using SQL Server 12.0.4213.0 and am new to advanced SQL queries. </p>
  
  <p>I have a <code>nvarchar</code> column named <strong>duration</strong> containing timespans in one of two formats: <code>39 Seconds</code> if the duration is under 1 minute, else the format is like <code>1 Day 18 Hours 2 Minutes</code>. Note that each Day/Hour/Minute/Second can be singular or plural. Also in the first format, if the day is 0, the Day will be omitted (same with Hour).</p>
  
  <p>I need to convert the duration to an integer representing total seconds. <code>1 Minute 30 Seconds</code> should return 90 and <code>39 Seconds</code> should return 39. </p>
  
  <p>I think this could be completed with a nested CASE statement. The dataset is very large so I want to be sure I am approaching this efficiently. Any help would be appreciated.</p>
  
  <p>One method I have working in PowerShell is to split the <strong>duration</strong> column on the space character, loop through the resultant array incrementing counter by 2, take <code>array[counter]</code> and multiply it by the corresponding seconds value determined by <code>array[counter+1]</code>, add the product to the total seconds sum.</p>