CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Praveenks
Praveenks

Reputation: 1496

Exact last character match in string

My requirement is to match the below characters with the values present in a table column.These characters should be the value in a string.

Characters :

JR
SR
II
III

Table Column values could be :

'MANFORTI JR','KRUMPAK III','PURDY II','MARRONE SR'

if the characters gets matched with the column values then just fetch the character values and consider it as suffix for the name.

Problem : The problem is occurring when trying to match II. The value is being fetched where the column value has III in the string.(Please refer attached screenshot)

Could you please suggest how to do the exact match ?

I come up with the below queries to match the string..

SELECT LAST_NAME A,
        CASE    WHEN REGEXP_INSTR(LAST_NAME, 'JR$') > 0
                THEN SUBSTR(LAST_NAME,REGEXP_INSTR(LAST_NAME, 'JR$'), LENGTH(LAST_NAME))
                WHEN REGEXP_INSTR(LAST_NAME, 'SR$') > 0
                THEN SUBSTR(LAST_NAME,REGEXP_INSTR(LAST_NAME, 'SR$'), LENGTH(LAST_NAME))       
                WHEN REGEXP_INSTR(LAST_NAME, 'II$') > 0  
                THEN SUBSTR(LAST_NAME,REGEXP_INSTR(LAST_NAME, '[II]$'), LENGTH(LAST_NAME))

        END SUFFIX
        FROM TBL_LAST_NAME
        WHERE LAST_NAME IN ('MANFORTI JR','KRUMPAK III','PURDY II','MARRONE SR')

Output :

enter image description here

Upvotes: 1

Views: 294

Answers (1)

MT0
MT0

Reputation: 167982

Prepend a space before the suffix (and you can also use LIKE rather than, more expensive, regular expressions) 

SELECT LAST_NAME AS A,
       CASE
       WHEN LAST_NAME LIKE '% JR'
         OR LAST_NAME LIKE '% SR'
         OR LAST_NAME LIKE '% II'
       THEN SUBSTR(LAST_NAME,-2)
       WHEN LAST_NAME LIKE '% III'
       THEN SUBSTR(LAST_NAME,-3)
       END AS SUFFIX
FROM   TBL_LAST_NAME
WHERE LAST_NAME IN ('MANFORTI JR','KRUMPAK III','PURDY II','MARRONE SR')

If you want to use regular expressions then:

SELECT LAST_NAME AS A,
       REGEXP_SUBSTR(
         last_name,
         '\W(SR|JR|II|III)$',   -- Match a non-word character then suffix
                                -- at end-of-string
         1,                     -- Start from the 1st character
         1,                     -- Find the 1st match
         'i',                   -- Case insensitive
         1                      -- Return the 1st capture group
       ) AS suffix
FROM   TBL_LAST_NAME
WHERE LAST_NAME IN ('MANFORTI JR','KRUMPAK III','PURDY II','MARRONE SR')

Upvotes: 2

Related Questions