sql - LPAD and RPAD on a CASE statement -
i'm trying pad left , right of case statement 3 asterisks. runs values don't show null values. ideas?
select p.patientfirstname || ' ' || p.patientlastname "patient" ,case when i.insuranceid null rpad(lpad('no insurance', 3, '*'), 3, '*') else i.insurancename end "insurance name" patient p full outer join insurance on (p.insuranceid = i.insuranceid);
as wrote in comment, if want constant string, use 1 , away padding:
select p.patientfirstname || ' ' || p.patientlastname "patient" ,case when i.insuranceid null '***no insurance***' else i.insurancename end "insurance name" patient p full outer join insurance on (p.insuranceid = i.insuranceid);
padding used ensure fixed-size string when working transforming strings of variable shorter sizes. see example:
select lpad('hola',7, '*'), lpad('namaste',7, '*'), lpad('hello',7, '*') dual;
***hola |namaste |**hello
select length(lpad('hola',7, '*')), length(lpad('namaste',7, '*')), length(lpad('hello',7, '*')) dual;
7 |7 |7
Comments
Post a Comment