I have a query in which I check between two values (Original and New) to see if there has been a change/update has been made to a record.
Essentially I am just comparing the fields:
WHEN PRFL_TBL.Original_Value != CHG_TBL.New_Value
THEN 'Segment Field Updated'
ELSE 'No changes'
END AS FIELD_CHANGED
However, what I am finding is that in some cases the fields compared are indeed not equal but the contents are the same:
For example the Original_Value = FKJFI, HGHER and the New_Value = HGHER, FKJFI or a little more complex Original_Value = THRID, GHERU, KFFEL, JFIEL and New_Value = JFIEL, GHERU, THRID, KFFEL.
The original logic is right; however in these examples above the logic is not accurate because they are the same codes but arranged differently. How can I check between the Original and New values to ensure that there is indeed a difference/change and not just a rearranging of the codes?
In my opinion, even if there is UDF to take care of comparison, there is chance due to data entry discrepancy that space is there among THRID, GHERU, KFFEL, JFIEL or even space in one keyword as specified example. So I suggest to write a UDF to meet this specific requirement.
A logic of extract the fields, trim spaces, concatenate and compare, may not work, because of many comparison cases and few more permutations and combinations. It is not a good idea too since it may be time-consuming and CPU intensive. You may miss out data.