I have a table:
CREATE TABLE test_val ( name VARCHAR2(10 CHAR) NOT NULL );
Let’s say I insert with the following:
INSERT INTO TEST_VAL (name) VALUES('££££££££££'); --data here may be variable characters string so size may vary from 10 to 40 bytes
Now the value in the table is 10 characters but 20 bytes.
And say I want to reduce the size of the column to 10 bytes, I don’t care about the data loss.
I do the following:
UPDATE test_val SET name = substr(name,1,10) WHERE lengthb(name)>10; ALTER TABLE test_val MODIFY name varchar2(10);
And I get an error that the value in the column is too big, because substr(name,1,10) leaves 10 symbols.
So my question is: Is there a substr alternative for substringing values to number of bytes, if not, is there another way to reduce column value so I could run alter table?