Find and replace combination of string and number

Other SQL Server Topics
1

we are getting as below in a table.

MONS ID CIF ID DETAILS
ASD345 12354767 CIF ID 12354767 can't found, it needs replacement
ASD569 354245254 Corresponding Learning found, cif id: 354245254
ASD508 64647474 Learning, curriculum combination; Cif Id is present in system 64647474; need to include in report
ASD402 12097655 Error details not found, can be skipped
ASD611 10924345 There is no Risk Associated with this record.

In the data in 3rd field (ie DETAILS field), if it has a combination of keyword : CIF ID ( it could be capitals, small, or combination of both) AND a number, they need to be replaced with MONS ID and data in MONS ID field respectively. please find expected output below.

MONS ID CIF ID DETAILS
ASD345 12354767 MONS ID ASD345 can't found, it needs replacement
ASD569 354245254 Corresponding Learning found, MONS ID: ASD569
ASD508 64647474 Learning, curriculum combination; MONS ID is present in system ASD508; need to include in report
ASD402 12097655 Error details not found, can be skipped
ASD611 10924345 There is no Risk Associated with this record.

Can someone please help me whether it is achievable ?

2

Can you post directly usable test data? You've given test data, but just as a "splat". Instead we need CREATE TABLE and INSERT statement(s) that produce the test data for actual use.

3

harishgg1 has a much better answer :wink:

4

hi

hope this helps

create data script

drop table #data

create table #data (MONS_ID varchar(200) , CIF_ID int , DETAILS varchar(200))
insert into #data select 'ASD345',12354767 ,'CIF ID 12354767 can''t found, it needs replacement'
insert into #data select 'ASD569',354245254 ,'Corresponding Learning found, cif id: 354245254'
insert into #data select 'ASD508',64647474 ,'Learning, curriculum combination; Cif Id is present in system 64647474; need to include in report'
insert into #data select 'ASD402',12097655 ,'Error details not found, can be skipped'
insert into #data select 'ASD611',10924345 ,'There is no Risk Associated with this record.'

select 
    *
	,replace(replace(lower(DETAILS),'cif id','MONS ID'),cast(CIF_ID as varchar),MONS_ID) 
from 
  #data

image
image1138×359 18.6 KB

2 Likes
5

Getting error while executing, can you please suggest..

ORA-00923: FROM keyword not found where expected

6

please post your code

this is a microsoft sql server forum

thank you

7

oh ok, thanks for reply.
we are using oracle database, here is where it is throwing above error.