CODEWITHSUNDEEP
sqloracle-databaseplsqlcase
user3352171
user3352171

Reputation: 53

Using a PLSQL CASE statement

I am trying to use a case statement to determine which select statement should be executed.
I want to check the third character of the variable :SSN. If the third character is a dash, use the first SQL statement to mask the first 5 numbers (XX-XXX1234). Otherwise, use the second SQL statement to mask the first 5 numbers (XXX-XX-1234).

What is the correct syntax for this?

select case
    when substr(:SSN, 3,1) = '-' then
    SELECT 'XX-XXX'||substr(:SSN, 7,4) INTO :MaskedSSN FROM DUAL
else
    SELECT 'XXX-XX-'||substr(:SSN, 8,4) INTO :MaskedSSN FROM DUAL
end

Upvotes: 0

Views: 418

Answers (3)

user272735
user272735

Reputation: 10648

Useless use of SQL context (i.e. select from dual) in PL/SQL is a very poor practise. Here we have a perfectly valid and simple PL/SQL only solution (this is a PL/SQL question anyway):

declare
  function mask_ssn(p_ssn in varchar2) return varchar2 is
  begin
    -- the OP explicitly asked for case-statement even old boring if-statement
    -- would do
    return case 
      when substr(p_ssn, 3, 1) = '-' then 'XX-XXX'  || substr(p_ssn, 7, 4)
      else                                'XXX-XX-' || substr(p_ssn, 8, 4)
    end;
  end;
begin
  dbms_output.put_line('masked: ' || mask_ssn('AB-CDE1234'));
  dbms_output.put_line('masked: ' || mask_ssn('ABC-CD-1234'));
end;
/

Output:

masked: XX-XXX1234
masked: XXX-XX-1234

(Note that I said useless use - there is many valid use scenarios, but this is not one of them.)

Upvotes: 0

Ilia Maskov
Ilia Maskov

Reputation: 1898

You can use SQL CASE expression for this

select case
    when substr(:SSN, 3,1) = '-' then
     'XX-XXX'||substr(:SSN, 7,4)  
else
     'XXX-XX-'||substr(:SSN, 8,4) 
end
INTO :MaskedSSN
from dual

Upvotes: 1

Avrajit Roy
Avrajit Roy

Reputation: 3303

Just an alternaive to try.Enjoy

SELECT DECODE(SUBSTR(:SSN, 3,1),'-','XX-XXX'
  ||SUBSTR(:SSN, 7,4),'XXX-XX-'
  ||SUBSTR(:SSN, 8,4))
INTO lv_var
FROM dual;

Upvotes: 0

Related Questions