title: How do I split an SQL column that is part string part number with variable length
body: <p>I have an ID column that uses letters and numbers: <code>A00055</code> or <code>AI000075</code> for example.
  The text values are in ascending order by ASCII code so I was going to do something like this:    </p>
  
  <pre><code>CAST((COALESCE(ASCII(SUBSTRING([FaultID],1,1)),SUBSTRING([FaultID],2,(len([FaultID])))))AS bigint) as FaultID1
  </code></pre>
  
  <p>However then I realized that the letters can be of variable length 1-3 characters in length so this errors out as soon as I hit a value with 2 letters. Is there a way to split the string on the first numeric value? Or is there a better way to solve this problem?  </p>