CODEWITHSUNDEEP
sqloracle-databasepositionsubstring
Auntie Anita
Auntie Anita

Reputation: 59

Oracle SQL - See if Value Contains Substrings from Three Other Values

I have three columns for a person's name (first middle last) and a column for a user ID. The purpose of this code is to find persons with a UserID that does not match their name. The UserID is created by taking the FIRST letter of the first name, the FIRST letter of the middle name and the FULL last name. Mary Jane Smith would have the UserID of MJSmith34. Random numbers are added to the end when there is more than one MJSmith, but this does not matter for what I need.

The code below compares the person's last name in two areas. If they do not match, it means that the person's name has been changed. What I need now is to check the UserID against the most current First Middle Last name to see if the UserID need to be changed (updated). The Pseudocode is what I need, but I am not sure if it can even be done. Please help!! 

Select DISTINCT
NL.spriden_id as "STU_ID", 
NL.SPRIDEN_LAST_NAME as "Last_Name" , 
NL.SPRIDEN_FIRST_NAME as "First_Name"


FROM SARADAP SA
JOIN SPRIDEN NM  -- NM is Name
ON SA.SARADAP_PIDM = NM.SPRIDEN_PIDM
JOIN SPRIDEN NL -- NL is null
ON SA.SARADAP_PIDM = NL.SPRIDEN_PIDM
JOIN IDTABLE ID
ON ST.SOMETABLE_PIDM = ID.IDTABLE_PIDM = 

WHERE
NM.SPRIDEN_CHANGE_IND LIKE '%N%' 
AND 
NL.SPRIDEN_CHANGE_IND IS NULL
AND
NL.SPRIDEN_ACTIVITY_DATE between sysdate - 10 and sysdate
AND 
NL.spriden_id LIKE 'A00%'
AND 
lower(NL.SPRIDEN_LAST_NAME) <> lower(NM.SPRIDEN_LAST_NAME)
AND
NL.SPRIDEN_ACTIVITY_DATE <> NM.SPRIDEN_ACTIVITY_DATE

Pseudocode - I know I need something with the POSITION of the characters.

I need help making this pseudocode into real code, please! :)

AND ID.USERID LIKE '%(NL.SPRIDEN_LAST_NAME)%'   

AND SUBSTRING (1st CHAR OF ID.USERID) LIKE 
SUBSTRING FIRST CHARACTER OF (NL.SPRIDEN_FIRST_NAME)

AND SUBSTRING (2nd char of ID.USERID) LIKE 
SUBSTRING FIRST CHAR OF (NL.SPRIDEN_MIDDLE_NAME)

Upvotes: 0

Views: 153

Answers (2)

Sylvain Leroux
Sylvain Leroux

Reputation: 51990

A slight variation on Multisync's good answer:

AND REGEXP_LIKE(ID.USERID, SUBSTR(NL.SPRIDEN_FIRST_NAME, 1, 1) 
                || SUBSTR(NL.SPRIDEN_MIDDLE_NAME, 1, 1) 
                || NL.SPRIDEN_LAST_NAME || '[0-9]*$', 'i')
--                                                     ^
--                                          case insensitive compare

That way you won't have false positive for cases such as James Tiberius Kirk => jtkirkwood and you get case insensitive compare "for free". The drawback here is I assume you don't have regex special characters as part of the name of your users...

Think about normalizing case too!

Upvotes: 1

Multisync
Multisync

Reputation: 8787

AND ID.USERID LIKE SUBSTR(NL.SPRIDEN_FIRST_NAME, 1, 1) 
                || SUBSTR(NL.SPRIDEN_MIDDLE_NAME, 1, 1) 
                || NL.SPRIDEN_LAST_NAME || '%'

|| is a concatenation operator

This condition means: check if USERID starts with the first symbol SPRIDEN_FIRST_NAME + the first symbol of SPRIDEN_MIDDLE_NAME + SPRIDEN_LAST_NAME

Upvotes: 2

Related Questions