Just for completeness, here is the revised code for parsing out the old ane new values into the final table...

IF UPPER(@p_details) = 'YES'

INSERT INTO Admin.Data_Changes (Table_name, Action, Key_fields, Key_values, Fieldname, Old_Value, New_Value, DW_load_dttm)

SELECT Table_name, Action, Key_fields, Key_values,

SUBSTRING('~'+ Changes +'~',N+1,CHARINDEX('=','~'+ Changes +'~',N+1)-N-1) AS Fieldname,

SUBSTRING(Changes,CHARINDEX('[',Changes,N+1)+1,( CHARINDEX('^^',Changes,N+1)-CHARINDEX('[',Changes,N+1)-1) ) AS Old_Value,

SUBSTRING(Changes,CHARINDEX('^^',Changes,N+1)+2,( CHARINDEX(']',Changes,N+1)-CHARINDEX('^^',Changes,N+1)-2) ) AS New_Value,

getdate()

FROM Admin.Tally a CROSS JOIN Stage.Data_Changes b

WHERE a.N < LEN('~'+ Changes +'~')

AND SUBSTRING('~'+ Changes +'~',N,1) = '~'

AND SUBSTRING('~'+ Changes +'~',N+1,1) <> '~'

AND table_name = @Table1

ELSE

INSERT INTO Admin.Data_Changes (Table_name, Action, Key_fields, Key_values, Fieldname, Old_Value, New_Value, DW_load_dttm)

SELECT Table_name, Action, Key_fields, Key_values,

SUBSTRING('~'+ Changes +'~',N+1,CHARINDEX('~','~'+ Changes +'~',N+1)-N-1) AS Fieldname,

null AS Old_Value,

null AS New_Value,

getdate()

FROM Admin.Tally a CROSS JOIN Stage.Data_Changes b

WHERE a.N < LEN('~'+ Changes +'~')

AND SUBSTRING('~'+ Changes +'~',N,1) = '~'

AND SUBSTRING('~'+ Changes +'~',N+1,1) <> '~'

AND table_name = @Table1