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:

UPDATED Image

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!

String before the first space is your second column value, String after the space is your unit value. USE charindex and substring function. — knkarthick24, Nov 19 '14 at 06:29
@knkarthick24 its not delimited with `space`, some data is one line only.. like `percentage` — Waelhi, Nov 19 '14 at 06:31
A better data set would really help those answering to help you best. There are a lot of nuances to your data that aren't properly shown in the question. — Chris, Nov 19 '14 at 07:25

Giorgos Betsos · Accepted Answer · 2014-11-19T08:10:02.710

17

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

edited Nov 19 '14 at 08:10

answered Nov 19 '14 at 06:43

Giorgos Betsos

71,379
9
63
98

i tried.. it works on some value but not with `dot` like this: `DECLARE @str VARCHAR(MAX) = '45.5%'` – Waelhi Nov 19 '14 at 07:02
the `.5%` is on `UNIT` Column. – Waelhi Nov 19 '14 at 07:03
@Unknownymous2 Sorry, I thought that numeric values are always integers – Giorgos Betsos Nov 19 '14 at 07:04
nope sir. sorry if its not indicated on my post that it also contain double numbers. but I believe your code is nearly to my expected result and you will update your answer. – Waelhi Nov 19 '14 at 07:11
'%[0-9][^0-9\.]%' may work for doubles in the PATINDEX – Chris Nov 19 '14 at 07:22
@GiorgosBetsos How to make it work for the following ECLARE str VARCHAR(MAX) = '25x2=50' ? Expected result is 25. (Upto the occurrence a character which is neither integer nor "." ) – LCJ Jan 24 '17 at 21:20
@Lijo Well, that's a different question. It wouldn't be appropriate to answer it here since it would contradict the intentions of the OP. Can you try writing a new post? – Giorgos Betsos Jan 25 '17 at 06:46

knkarthick24 · Answer 2 · 2014-11-19T08:20:20.743

Here is my answer. Check output in SQLFiddle for the same.

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

Updated Version to handle 99.5 ug\ml

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

Updated Version: To handle 1 000 ug\ml,20 000ug\ml

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

Check out SQLFiddle for the same.

its not working with `double` number like this: `45.5%` the `.5%` is on `subsrtunit` Column. — Waelhi, Nov 19 '14 at 07:15
try this: `1 000ug\ml`(with space on every 3 digit and no space on number and unit) it seems not working on me. — Waelhi, Nov 19 '14 at 07:36
Firstly why are you having this kind of data in place? Whatever you have asked so for has some sense. This is nowhere valid scenario. Please consider to clean up your data accordingly. — knkarthick24, Nov 19 '14 at 07:38

score 2 · Answer 3 · answered Nov 19 '14 at 06:33

Would something like this work? Based on the shown data it looks like it would.

Apply it to your data set as a select and if you like the results then you can make an update from 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

its not delimited with `space`, other value has no `spacing` like `percentage`: `WITH cte as (SELECT 'ug/mL' ConcUnit, 500 as [Numeric], '' as Unit UNION ALL SELECT '2000%', NULL, '')` — Waelhi, Nov 19 '14 at 06:39

Dudi Konfino · Answer 4 · 2014-11-19T07:58:48.023

2

change @concunit & @unitx Respectively

 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

edited Nov 19 '14 at 07:58

answered Nov 19 '14 at 06:54

Dudi Konfino

1,126
2
13
24

not working on `percentage` sir. it gives me error: `Msg 537, Level 16, State 2, Line 5 Invalid length parameter passed to the LEFT or SUBSTRING function.` – Waelhi Nov 19 '14 at 07:19
i use this: `DECLARE @concunit varchar(10)='45.5%'` – Waelhi Nov 19 '14 at 07:19
DECLARE var concunit varchar(10)='45.5%' DECLARE var unitx varchar(10)='%' – Dudi Konfino Nov 19 '14 at 07:20

score 0 · Answer 5 · edited Oct 19 '21 at 07:33

I had the same dilemma, but in my case the alpha's were in front of the numerics.

So using the logic that @Giorgos Betsos added to his answer, I just reversed it.

I.e., when your input is :

abc123

You can split it like this:

declare @input  varchar(30) = 'abc123'

    select
        replace(@input,reverse(LEFT(reverse(@input), PATINDEX('%[0-9][^0-9]%', reverse(@input) ))),'')  Alpha
    ,   reverse(LEFT(reverse(@input), PATINDEX('%[0-9][^0-9]%', reverse(@input) ))) Numeric

Results :

split alpha and numeric using sql

5 Answers5

Linked