Extracting Multiple Numerical Values from Text

Question

048(70F-Y),045(DDI-Y),454(CMDE-Y)

I have the above data in a column field, I need to extract each number before the, so in the above example I would want to see 048, 045, 454.

Note the data in the field will change in each record in the above you have 3 sets of numbers. Sometimes you may have just one set or 6 sets. I just need to capture all sets of numbers that are to the left of the (.

Ideally I would want the results to show in a new column like below. I have tried a few things and gotten no where any help would be greatly appreciate.

I would expect the result to look like the below:

+----------+-----------------------------------+---------------+
| EventId  |            PAEditTypes            |     Edits     |
+----------+-----------------------------------+---------------+
|  6929107 | 082(SPA-Y),177(QL-Y)              |      082, 177 |
| 26534980 | 048(70F-Y),045(DDI-Y),454(CMDE-Y) | 045, 048, 454 |
+----------+-----------------------------------+---------------+

I would prefer to use a loop and that is what I am trying to develop but not sure how to set it up. — James, Jul 25 '19 at 23:47
Use `string_split()` on commas. Then extract everything before initial paren using `left()` and `charindex()`. Recombine with `listagg()` Is order important? — shawnt00, Jul 26 '19 at 01:07
I used the string_spilit but not sure how to do the left and charindex() piece you recommend. — James, Jul 26 '19 at 11:58
SELECT evt.EventId , s.value FROM [PBM_Ops_Reporting].[Pahub].[Events] AS EVT CROSS APPLY STRING_SPLIT(evt.[PAEditTypes], '(') S — James, Jul 26 '19 at 11:58
EventId value 6929107 082 6929107 SPA-Y),177 6929107 QL-Y) 26534980 048 26534980 70F-Y),045 26534980 DDI-Y),454 26534980 CMDE-Y) — James, Jul 26 '19 at 11:59

score 0 · Answer 1 · answered Jul 28 '19 at 12:48

You can get desired output with the following step:

use string_split with cross apply to isolate each item
use left to get only the first part of each item together with CHARINDEX to know where you have to stop
use STRING_AGG to build the final result, adding WITHIN GROUP clause to enforce ordering (if ordering is not important just remove WITHIN GROUP clause)

This is a TSQL sample that should work:

declare @tmp table ( EventId  varchar(50), PAEditTypes varchar(200) )

insert into @tmp values
     ('6929107' ,'082(SPA-Y),177(QL-Y)'             ) 
    ,('26534980','048(70F-Y),045(DDI-Y),454(CMDE-Y)') 

select 
     EventId
   , PAEditTypes
   , STRING_AGG(left(value,CHARINDEX('(',value)-1),', ') WITHIN GROUP (ORDER BY value ASC) as Edits
from 
    @tmp
        cross apply 
    string_split(PAEditTypes, ',')
group by 
      EventId
    , PAEditTypes
order by
    EventId desc

Output:

Extracting Multiple Numerical Values from Text

1 Answers1