CODEWITHSUNDEEP
sqloracle-database
John
John

Reputation: 299

Trim table column length over x charcters

I have the below table and column. The column is varchar(8). I need to trim characters over 7.

TableA

LastName
 Smith
 McKinney

My below SQL TRIMs all rows. How do we only trim length over 7 characters?

update TEST set LASTNAME = SUBSTR(LASTNAME,1,LENGTH(LASTNAME)-1);

Upvotes: 0

Views: 445

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

Just check the starting length:

update TEST set LASTNAME = SUBSTR(LASTNAME,1,LENGTH(LASTNAME)-1)
where LENGTH(LASTNAME) > 7;

1 row updated.

select * from test;

LASTNAME
--------
Smith
McKinne

You could replace LENGTH(LASTNAME)-1 with numeric literal 7 in this case.

Upvotes: 3

Related Questions