Reputation: 77
I have a table TEST_TABLE having a column with name as LNAME having VARCHAR2 data type. The requirement is that the value of LNAME must be minimum of 2 characters.
1- Create table
CREATE TABLE TEST_TABLE
(
LNAME VARCHAR2(4000)
);
2 - insert record with length 1
INSERT INTO TEST_TABLE (LNAME) VALUES ('A');
LNAME is having value 'A'
.
I need to transform this value to 'A '
. [With an added space to ensure minimum length condition is satisfied].
Note: need to be done using CASE...WHEN
My attempt:
select
CASE
WHEN LENGTH(LNAME) < 2
THEN RPAD(LNAME,2)
ELSE ''
END LNAME
from TEST_TABLE;
Using the below query to check the length:
select LENGTH(LNAME) from TEST_TABLE;
The length returns as 1 and not 2 as expected. Is RPAD the right way to append the space to the end of "A"?
Upvotes: 0
Views: 71
Reputation: 168806
Use a CHECK
constraint on the column:
CREATE TABLE TEST_TABLE
(
LNAME VARCHAR2(4000) CONSTRAINT TEST_TABLE__LNAME__MINLENGTH2 CHECK ( LENGTH( LNAME ) >= 2 )
);
Then you can use your RPAD
in your insert statement if the value is too short:
INSERT INTO TEST_TABLE ( LNAME ) VALUES ( RPAD( 'A', 2 ) );
or you could use a MERGE
statement:
MERGE INTO TEST_TABLE t
USING ( SELECT 'A' AS value FROM DUAL ) v
ON ( 0 = 1 )
WHEN NOT MATCHED THEN
INSERT ( LNAME ) VALUES ( CASE WHEN LENGTH( v.value ) < 2 THEN RPAD( v.value, 2 ) ELSE v.value END );
Or an anonymous PL/SQL block:
DECLARE
value TEST_TABLE.LNAME%TYPE := 'B';
BEGIN
INSERT INTO test_table ( lname ) VALUES ( CASE WHEN LENGTH( value ) < 2 THEN RPAD( value, 2 ) ELSE value END );
END;
/
db<>fiddle here
If you want to change your existing data then use an UPDATE
statement:
UPDATE test_table
SET lname = RPAD( lname, 2 )
WHERE LENGTH( lname ) < 2
Upvotes: 0
Reputation: 1271241
rpad()
should work, but your else
might be causing problems:
select (case when length(lname) < 2 then rpad(lname, 2)
else lname
end) as lname
from TEST_TABLE;
Upvotes: 2