title: split alpha and numeric using sql
body: <p>I have a table and it has a 3 columns. The <strong><code>first column</code></strong> is the data that contains value(numeric) and unit(percentage and etc..), the second column is <strong><code>numeric column</code></strong>, the third is <strong><code>Unit column</code></strong>. What I want to do is split the numeric and the unit from the first column then put those split-ted data to its designated column.</p>
  
  <p>Here is my table:</p>
  
  <p><img src="https://i.stack.imgur.com/z5QaW.png" alt="UPDATED Image"></p>
  
  <p>I tried this function:<a href="https://stackoverflow.com/questions/9629880/extract-numbers-from-a-text-in-sql-server">SO link here...</a>, it really does splitting alpha and numeric but then I'm new in using SQL Function, my problem there is the parameter must be in string <code>STRING</code>, so what I did is change it to Sub Query but it gives me error.</p>
  
  <p>Sample COde:</p>
  
  <p><strong>SQL FUNCTION:</strong></p>
  
  <pre><code>create function [dbo].[GetNumbersFromText](@String varchar(2000))
  returns table as return
  (
    with C as
    (
      select cast(substring(S.Value, S1.Pos, S2.L) as int) as Number,
             stuff(s.Value, 1, S1.Pos + S2.L, '') as Value
      from (select @String+' ') as S(Value)
        cross apply (select patindex('%[0-9]%', S.Value)) as S1(Pos)
        cross apply (select patindex('%[^0-9]%', stuff(S.Value, 1, S1.Pos, ''))) as S2(L)
      union all
      select cast(substring(S.Value, S1.Pos, S2.L) as int),
             stuff(S.Value, 1, S1.Pos + S2.L, '')
      from C as S
        cross apply (select patindex('%[0-9]%', S.Value)) as S1(Pos)
        cross apply (select patindex('%[^0-9]%', stuff(S.Value, 1, S1.Pos, ''))) as S2(L)
      where patindex('%[0-9]%', S.Value) &gt; 0
    )
    select Number
    from C
  )
  </code></pre>
  
  <p><strong>SELECT STATEMENT with SUB Query:</strong></p>
  
  <pre><code>declare @S varchar(max)
  select number from GetNumbersFromText(Select SomeColm From Table_Name) option (maxrecursion 0)
  </code></pre>
  
  <p>BTW, im using sql server 2005.</p>
  
  <p>Thanks!</p>