user3583313 user3583313 - 7 months ago 14
SQL Question

LPAD and RPAD on a CASE statement

I'm trying to pad to the left and right of a case statement with 3 asterisks. It runs but the values don't show up for the Null values. Any ideas?

SELECT p.patientfirstname || ' ' || p.patientlastname AS "Patient"
,CASE WHEN i.insuranceid IS NULL THEN RPAD(LPAD('No insurance', 3, '*'), 3, '*')
ELSE i.insurancename
END "Insurance Name"
FROM patient p
FULL OUTER JOIN insurance i ON (p.insuranceid = i.insuranceid);

Answer

As I wrote in my comment, if you want a constant string, just use one and do away with the padding:

 SELECT p.patientfirstname || ' ' || p.patientlastname AS "Patient"  
,CASE WHEN i.insuranceid IS NULL THEN '***No insurance***'
 ELSE i.insurancename 
 END "Insurance Name"
FROM patient p 
FULL OUTER JOIN insurance i ON (p.insuranceid = i.insuranceid);

Padding is used to ensure a fixed-size string when working with transforming strings that are of variable shorter sizes. See example:

select lpad('Hola',7, '*'),  lpad('Namaste',7, '*'), lpad('Hello',7, '*') from dual;

***Hola |Namaste |**Hello

select length(lpad('Hola',7, '*')),  length(lpad('Namaste',7, '*')), length(lpad('Hello',7, '*')) from dual;

7 |7 |7

Comments