Separate alpha and numeric using sql

Question

I have a table and it has 3 columns. - this is data that contains the value (numeric) and unit (in percent, etc.), the second column , the third - . What I want to do is to separate the numeric and units from the first column, and then put this data broken down into the specified column. first column numeric column Unit column

Here is my table:

I tried this function: The SO link is here ... it really separates alpha and numeric, but then I am new to using the SQL function, my problem is there the parameter should be in the string STRING, so I did it for Sub Query, but it gives me an error .

COde example:

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
)

SELECTING AN APPLICATION WITH A SUB REQUEST:

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

BTW, im using SQL Server 2005.

Thank!

Answer 1

, :

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

.

, :

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

:

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

EDIT:

, :

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

.

, :

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

.

:

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

, .

, , :

Input:

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

:

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

Input:

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

:

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

:

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

:

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

Answer 2

. SQLFiddle .

create TABLE temp
(
      string NVARCHAR(50)
)

INSERT INTO temp (string)
VALUES 
    ('4000 ug\ml'),
    ('2000 ug\ml'),
    ('%'),
    ('ug\ml')

SELECT subsrtunit,LEFT(subsrtnumeric, PATINDEX('%[^0-9]%', subsrtnumeric+'t') - 1)
FROM (
    SELECT subsrtunit = SUBSTRING(string, posofchar, LEN(string)),
  subsrtnumeric = SUBSTRING(string, posofnumber, LEN(string))
    FROM (
        SELECT string, posofchar = PATINDEX('%[^0-9]%', string),
      posofnumber = PATINDEX('%[0-9]%', string)
        FROM temp
    ) d
) t

99,5 \

create TABLE temp
(
      string NVARCHAR(50)
)

INSERT INTO temp (string)
VALUES 
    ('4000 ug\ml'),
    ('2000 ug\ml'),
    ('%'),
    ('ug\ml'),
    ('99.5 ug\ml')

SELECT subsrtunit,LEFT(subsrtnumeric, PATINDEX('%[^0-9.]%', subsrtnumeric+'t') - 1)
FROM (
    SELECT subsrtunit = SUBSTRING(string, posofchar, LEN(string)),
  subsrtnumeric = SUBSTRING(string, posofnumber, LEN(string))
    FROM (
        SELECT string, posofchar = PATINDEX('%[^0-9.]%', string),
      posofnumber = PATINDEX('%[0-9.]%', string)
        FROM temp
    ) d
) t

: 1 000 \, 20 000\

create TABLE temp
(
      string NVARCHAR(50)
)

INSERT INTO temp (string)
VALUES 
    ('4000 ug\ml'),
    ('2000 ug\ml'),
    ('%'),
    ('ug\ml'),
    ('99.5 ug\ml'),
    ('1 000 ug\ml'),
    ('20 000ug\ml')

SELECT substring(replace(subsrtunit,' ',''),PATINDEX('%[0-9.]%', replace(subsrtunit,' ',''))+1,len(subsrtunit)),
LEFT(replace(subsrtnumeric,' ',''), PATINDEX('%[^0-9.]%', replace(subsrtnumeric,' ','')+'t') - 1)
FROM (
    SELECT subsrtunit = SUBSTRING(string, posofchar, LEN(string)),
  subsrtnumeric = SUBSTRING(string, posofnumber, LEN(string))
    FROM (
        SELECT string, posofchar = PATINDEX('%[^0-9.]%', replace(string,' ','')),
      posofnumber = PATINDEX('%[0-9.]%', replace(string,' ',''))
        FROM temp
    ) d
) t

SQLFiddle .

Answer 3

Something like this work? Based on the data shown, it looks like it will be.

Apply it to your dataset as a choice, and if you like the results, you can update it.

WITH cte as (SELECT 'ug/mL' ConcUnit, 500 as [Numeric], '' as Unit 
   UNION ALL SELECT '2000 ug/mL',     NULL,             '')

SELECT
    [ConcUnit]                  as [ConcUnit],
    [Numeric]                   as [Original Numeric],
    [Unit]                      as [Original Unit],
    CASE WHEN ConcUnit LIKE '% %' THEN 
        SUBSTRING(ConcUnit, 1, CHARINDEX(' ', ConcUnit) - 1) 
        ELSE [Numeric] END      as [New Numeric],
    CASE WHEN ConcUnit LIKE '% %' 
        THEN SUBSTRING(ConcUnit, CHARINDEX(' ', ConcUnit) + 1, LEN(ConcUnit)) 
        ELSE ConcUnit END       as [New Unit]
FROM cte

Answer 4

change @concunit and @unitx accordingly

 DECLARE @concunit varchar(10)='45.5%'
DECLARE @unitx varchar(10)='%'

BEGIN
SELECT RTRIM(SUBSTRING( @concunit , 1 , CHARINDEX( @unitx , @concunit
                                              ) - 1
                )) AS Number, 
       RTRIM(SUBSTRING( @concunit , CHARINDEX( @unitx , @concunit
                                          ) , LEN( @concunit
                                                 ) - (CHARINDEX( @unitx , @concunit
                                                               ) - 1)
                )) AS Unit

end