CODEWITHSUNDEEP
sqloracle-database
gooner_psy
gooner_psy

Reputation: 77

To add space to a VARCHAR column value to ensure a minimum length of 2

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

Answers (2)

MT0
MT0

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

Gordon Linoff
Gordon Linoff

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

Related Questions