Checking for difference in Strings

Database
Enthusiast

Checking for difference in Strings

Hi,

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:

That is,

CASE
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?

Thanks.

Teradata Noob.

1 REPLY
Enthusiast

Re: Checking for difference in Strings

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.