How to check if first two characters of words are equal between columns in Oracle sql

user_6497054 0
Viewed 712

I am trying to compare if an original and masked string are equal. I want to compare whether the first 2 characters of every word are the same or not. I am using Oracle sql 12c.

For example, Lets say I have a User table and columns name_request and name_response, the comparison should return true for these records:

| name_request       | name_response      |
+--------------------+--------------------+
| steve jobs         | st*** jo**         |
| sergey george brin | se**** ge**** br** |

and false for these records:

| bill gates         | jo** he***         |
| steve jhon wozniak | he*** va**         |

Can anyone help me about it?

oracle sql
2 Answers

If you use SQL Server 2017+ and want to try a JSON-based approach, you may use the following statement. The important part in this approach is the transformation of the names into a valid JSON array (steve jobs is transformed into ["steve","jobs"]). After that, using OPENJSON(), you can parse this array and match every part from the original and the encrypted names:

Table:

CREATE TABLE Users (
   name_request varchar(100), 
   name_response varchar(100)
)
INSERT INTO Users
   (name_request, name_response)
VALUES   
   ('steve jobs',         'st*** jj**'),
   ('steve jobs',         'st*** jo**'),
   ('sergey george brin', 'se**** ge**** br**'),
   ('bill gates',         'jo** he***'),
   ('steve jhon wozniak', 'he*** va**')

Statement:

SELECT 
   u.*, 
   CASE WHEN a.words = a.matches THEN 'true' ELSE 'false' END AS result
FROM Users u
OUTER APPLY (
   SELECT 
      SUM(1) AS [words],
      SUM(CASE WHEN LEFT(j1.[value], 2) = LEFT(j2.[value], 2) THEN 1 ELSE 0 END) AS [matches]
   FROM OPENJSON(CONCAT('["', REPLACE(STRING_ESCAPE(u.name_request, 'json'), ' ', '","'), '"]')) j1
   FULL JOIN OPENJSON(CONCAT('["', REPLACE(STRING_ESCAPE(u.name_response, 'json'), ' ', '","'), '"]')) j2 
      ON j1.[key] = j2.[key]
) a

Result:

----------------------------------------------
name_request        name_response       result
----------------------------------------------
steve jobs          st*** jj**          false
steve jobs          st*** jo**          true
sergey george brin  se**** ge**** br**  true
bill gates          jo** he***          false
steve jhon wozniak  he*** va**          false
user_65780800

I would just use like:

select u.*,
       (case when name_request like replace(name_response, '*', '_')
             then 'true' else 'false'
        end)
from users u;

Your use of * seems equivalent to the _ in like.

user_11440350
Related