sql server - split alpha and numeric using sql

Question

I have a table and it has a 3 columns. The first column is the data that contains value(numeric) and unit(percentage and etc..), the second column is numeric column, the third is Unit column. 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.

Here is my table:

I tried this function:SO link here..., 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 STRING, so what I did is change it to Sub Query but it gives me error.

Sample COde:

SQL FUNCTION:

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) > 0
  )
  select Number
  from C
)

SELECT STATEMENT with SUB Query:

declare @S varchar(max)
select number from GetNumbersFromText(Select SomeColm From Table_Name) option (maxrecursion 0)

BTW, im using sql server 2005.

Thanks!

Answer 1

If the numeric part is always at the beginning, then you can use this:

PATINDEX('%[0-9][^0-9]%', ConcUnit)

to get the index of the last digit.

Thus, this:

DECLARE @str VARCHAR(MAX) = '4000 ug/ML' 

SELECT LEFT(@str, PATINDEX('%[0-9][^0-9]%', @str )) AS Number,
       LTRIM(RIGHT(@str, LEN(@str) - PATINDEX('%[0-9][^0-9]%', @str ))) As Unit

gives you:

Number  Unit
-------------
4000    ug/ML

EDIT:

If numeric data include double values as well, then you can use this:

SELECT LEN(@str) - PATINDEX ('%[^0-9][0-9]%', REVERSE(@str))

to get the index of the last digit.

Thus, this:

SELECT LEFT(@str, LEN(@str) - PATINDEX ('%[^0-9][0-9]%', REVERSE(@str)))

gives you the numeric part.

And this:

SELECT LEFT(@str, LEN(@str) - PATINDEX ('%[^0-9][0-9]%', REVERSE(@str))) AS Numeric,
       CASE 
          WHEN CHARINDEX ('%', @str) <> 0 THEN LTRIM(RIGHT(@str, LEN(@str) - CHARINDEX ('%', @str)))
          ELSE LTRIM(RIGHT(@str, PATINDEX ('%[^0-9][0-9]%', REVERSE(@str))))
       END AS Unit

gives you both numberic and unit part.

Here are some tests that I made with the data you have posted:

Input:

DECLARE @str VARCHAR(MAX) = '50 000ug/ML'

Output:

Numeric Unit
------------
50 000  ug/ML

Input:

DECLARE @str VARCHAR(MAX) = '99.5%'

Output:

Numeric Unit
------------
99.5    

Input:

DECLARE @str VARCHAR(MAX) = '4000 . 35 % ug/ML'

Output:

Numeric     Unit
------------------
4000 . 35   ug/ML