Search code examples
sqlstringdifferencecpu-wordletter

Find difference between two characters in SQL

I have a table with 2 columns

column1   column2
abcd      abdc
abcr      dfgh
cvge      cbge

I want to know the sql query to get a third column with the results showing the difference between the 2 fields in such a way

column1   column2    calculated field
abcd      abdc       2
abcr      dfgh       0
cvge      cbge       3
Solution

  • If the fields are always four characters, you can use a case expression. It is something like this:

    select ( (case when substr(field1, 1, 1) = substr(field2, 1, 1) then 1 else 0 end) +
         (case when substr(field1, 2, 1) = substr(field2, 2, 1) then 1 else 0 end) +
         (case when substr(field1, 3, 1) = substr(field2, 3, 1) then 1 else 0 end) +
         (case when substr(field1, 4, 1) = substr(field2, 4, 1) then 1 else 0 end)
       ) as calculated

    Note that all databases support the functionality of substr(), but in some databases, the function might have a different name.