-3

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

Thom A
  • 88,727
  • 11
  • 45
  • 75
Kavita GK
  • 11
  • 3
  • 1
    As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K Feb 03 '22 at 10:01
  • 1
    Fix your design; don't store delimited data in your database in the first place. *That* is the real problem. – Thom A Feb 03 '22 at 10:02
  • T-SQL is a language poorly suited to string manipulation like this; if possible, shift it to the client side, as this is a trivial exercise in most programming languages. – Jeroen Mostert Feb 03 '22 at 10:06
  • https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string – RF1991 Feb 03 '22 at 10:27

1 Answers1

1

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))

insert into @temp2
Select Comment,[value] as numeric_values from @temp1
CROSS APPLY string_split(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

Kavita GK
  • 11
  • 3