Reduce varchar2(n char) size to varchar2(n) when value length = n but lentgthb = x*n

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?

Thanks


Source: oracle

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.