SQLDeveloper – Check for a column within another column

I am currently trying to query if a Last_Name column has the suffix added to the end of a Last_Name.

I have a Suffix Table(SUFFIX) which stores the available suffixes (I, II, III, IV, V, Jr., Sr.) however I am running into trouble when I have performed the below query as it shows all the last names even if it does not contain the SUFFIX at then end of LAST_NAME column.

SELECT
NAMES.RECORD_NAME
, NAMES.LAST_NAME
, NAMES.FIRST_NAME
, NAMES.SUFFIX_C AS SUFFIX_ID
, SUFFIX.name AS SUFFIX
, USERS.USER_ID
, NAMES.RECORD_ID

FROM
USERS
left join NAMES ON USERS.NAME_RECORD_ID = NAMES.RECORD_ID
left join SUFFIX ON NAMES.SUFFIX_C = SUFFIX.suffix_c

WHERE
NAMES.SUFFIX_C IS NOT NULL
AND  SUFFIX.name in (select NAMES.LAST_NAME from NAMES)

What I am trying to make is the following: If Suffix = IV and last name is SmithIV (contains the suffix)at the end of the last name to display on my results.

For some reason the suffix was added to some last names and I am trying to determine how many are affected.

Answer

This is how I understood the question.

Lines #1 – 15 represent sample data; interesting part are lines #19 – 21 which select the last “several” characters from the last name, where “several” equals length of the suffix itself.

SQL> with
  2  -- sample data
  3  names (first_name, last_name, suffix_c) as
  4    (select 'Little', 'Foot'   , 1 from dual union all
  5     select 'Big'   , 'FootIV' , 4 from dual union all
  6     select 'Scott' , 'TigerSr', 5 from dual union all
  7     select 'Mike'  , 'Lion'   , 6 from dual
  8    ),
  9  suffix (suffix_c, name) as
 10    (select 1, 'I'  from dual union all
 11     select 2, 'II' from dual union all
 12     select 4, 'IV' from dual union all
 13     select 5, 'Sr' from dual union all
 14     select 6, 'Jr' from dual
 15    )
 16  -- query you might need
 17  select n.*
 18  from names n join suffix s on s.suffix_c = n.suffix_c
 19  where substr(n.last_name,
 20               length(n.last_name) - length(s.name) + 1,
 21               length(s.name)
 22              ) = s.name;

FIRST_NAME LAST_NAME    SUFFIX_C
---------- ---------- ----------
Big        FootIV              4
Scott      TigerSr             5

SQL>