CODEWITHSUNDEEP
sqloracle-database
monika
monika

Reputation: 67

Full name validation

I want to validate the full name field (full name should be the concatenation of first name, middle name, & last name using only a single space between them), I am using the below logic:

If A.FULL_NAME <> (A.FIRST_NAME||' '||A.MIDDLE_NAMES||' '||A.LAST_NAME) THEN
            X_STATUS:=false;
            X_REMARKS:='FULL NAME IS INCORRECT';
            RETURN;
        END IF;

The above logic works when all types name (first, middle & last name ) exist but in case there is no last name then it will take 2 space at the end of the string & 3 space between the first name & last name when there is no middle name. Give me the optimal solution .Thank You.

Upvotes: 0

Views: 650

Answers (3)

William Robertson
William Robertson

Reputation: 16001

The full case when a.first_name is not null approach will work but seems to me painfully verbose. I would go with something more compact:

rtrim(ltrim(a.first_name||' ') || ltrim(a.middle_names||' ') || a.last_name)

for example,

with people (first_name, middle_names, last_name) as
     ( select 'Olivia', 'Newton', 'John' from dual union all
       select 'John', null, 'Travolta' from dual union all
       select 'Madonna', null, null from dual union all
       select null, 'Sting', null from dual union all
       select null, null, 'Cher' from dual )
select rtrim(ltrim(a.first_name||' ') || ltrim(a.middle_names||' ') || a.last_name) as full_name
from   people a

Upvotes: 2

marcothesane
marcothesane

Reputation: 6731

With some test data - The Boolean expression gets a bit complex, but works and is flexible ...

WITH
a(first_name,middle_name,last_name,full_name) AS (
          SELECT 'Marcus','Aurelius','Antoninus','Marcus Aurelius Antoninus'
UNION ALL SELECT 'Gaius','Julius','Caesar','Gaius Julius Caesar'
UNION ALL SELECT 'Seneca',NULL,NULL,'Seneca'
UNION ALL SELECT 'John','Wick', NULL,'John Wick'
)
SELECT
  *
, CASE WHEN a.first_name IS NULL
    THEN ''
    ELSE ' '||a.first_name
  END 
||CASE WHEN a.middle_name IS NULL
    THEN ''
    ELSE ' '||a.middle_name
  END 
||CASE WHEN a.last_name IS NULL
    THEN ''
    ELSE ' '||a.last_name
  END  AS tested
FROM a
WHERE LTRIM(
   CASE WHEN a.first_name IS NULL
     THEN ''
     ELSE ' '||a.first_name
   END 
 ||CASE WHEN a.middle_name IS NULL
     THEN ''
     ELSE ' '||a.middle_name
   END 
 ||CASE WHEN a.last_name IS NULL
     THEN ''
     ELSE ' '||a.last_name
  END
) = full_name
;
-- out  first_name | middle_name | last_name |         full_name         |           tested           
-- out ------------+-------------+-----------+---------------------------+----------------------------
-- out  Marcus     | Aurelius    | Antoninus | Marcus Aurelius Antoninus |  Marcus Aurelius Antoninus
-- out  Gaius      | Julius      | Caesar    | Gaius Julius Caesar       |  Gaius Julius Caesar
-- out  Seneca     |             |           | Seneca                    |  Seneca
-- out  John       | Wick        |           | John Wick                 |  John Wick

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231671

First off, I'd question why you'd have columns for full_name in addition to first_name, middle_name, and last_name that could possibly be out of sync. You'd normally store the components of the name and have a function that returns the full_name based on that (or use that function to create a derived column).

That said, I would guess that you'd want to compare the full_name against

a.first_name ||
  (case when a.middle_name is not null 
        then ' ' || a.middle_name
        else null
     end) ||
  (case when a.last_name is not null 
        then ' ' || a.last_name
        else null
     end)

Upvotes: 5

Related Questions