Extract numbers from string in sql-CodePudding

In my Table there is a column called Comment and it contains data like

input
Comment

| 22 | 22 | INTERNAL AUDIT | NM | OK                  
| Multiplied by 4 | 32 | 32 | INTERNAL AUDIT | TR | None         
| 19 | 17 | INTERNAL AUDIT | LM | FIXED       
| REF#R7F282CT

we need to extract only numbers from this comment column and update into other column

need output like this like:

col1   col2
22     22              
32     32        
19     17      
null   null

input image

Input result

Output image

Output result

CodePudding user response：

Using Split Function

declare @temp1 table (Comment varchar(255))
insert into @temp1 values ('| 22 | 22 | INTERNAL AUDIT | NM | OK')
insert into @temp1 values ('| Multiplied by 4 | 32 | 32 | INTERNAL AUDIT | TR | None')
insert into @temp1 values ('| 19 | 17 | INTERNAL AUDIT | LM | FIXED')
insert into @temp1 values ('| REF#R7F282CT')

declare @temp2 table (Comment varchar(255),numeric_values varchar(100))

Split function : dbo.fn_split_string_delimeter

insert into @temp2
Select Comment,[value] as numeric_values from @temp1
CROSS APPLY dbo.fn_split_string_delimeter(Comment,'|')
WHERE ISNUMERIC([value]) = 1

--select * from @temp2

SELECT Comment,
MAX(CASE WHEN Row_Num = 1 THEN numeric_values END) Col1,
MAX(CASE WHEN Row_Num = 2 THEN numeric_values END) Col2
FROM (
SELECT Comment,numeric_values,
ROW_NUMBER() OVER(PARTITION BY comment ORDER BY (select null)) as Row_Num
FROM @temp2 ) d GROUP BY Comment