I'm looking for a way to dynamically truncate a string to the max size of a varchar2 column.

See example below.

Table definition

CREATE TABLE some_table
( 
  log varchar2(50)
);

Code

DECLARE
  v_str some_table.log%TYPE;
BEGIN
  v_str := SUBSTR('a text longer than 50 chars ...', 1,  50 /*HOW TO GET 50 DYNAMICALLY HERE ?*/ );
END;

Any ideas?

CodePudding user response：

select char_length 
  from user_tab_columns 
 where table_name = 'SOME_TABLE' 
   and column_name = 'LOG';

would return a value of 50 (the length of the log column in the some_table table)

CodePudding user response：

Getting (and caching) the column length is probably the way to go, but just for fun and riffing from an approach in this discussion:

DECLARE
  v_str some_table.log%TYPE;
  v_ret pls_integer;
  v_len pls_integer;
BEGIN
  v_ret := anydata.convertchar('-').getchar(v_str);
  v_len := length(v_str);
  dbms_output.put_line(v_len);

  v_str := substr('a text longer than 50 chars, a text longer than 50 chars, a text longer than 50 chars...', 1,  v_len);
  dbms_output.put_line(v_str);
END;
/

dbms_output:
50
a text longer than 50 chars, a text longer than 50

Because it's using convertchar and getchar rather than the varchar2 versions, the result is the literal char - padded to its maximum length based on the variable's data type.

db<>fiddle

You can read more about anydata at Oracle-base or in the documentation.

You could still cache that, or work it out on exception. If you were going to do this often and performance was acceptable without caching/catching, you could possibly wrap it in a procedure:

CREATE PROCEDURE trunc_string(p_str out varchar2, p_val varchar2)
AS
  v_ret pls_integer;
  v_len pls_integer;
BEGIN
  v_ret := anydata.convertchar('-').getchar(p_str);
  v_len := length(p_str);
  p_str := substr(p_val, 1,  v_len);
END;
/

and then call that as:

DECLARE
  v_str1 some_table.log%TYPE;
  v_str2 varchar2(30);
BEGIN
  trunc_string(v_str1, 'a text longer than 50 chars, a text longer than 50 chars, a text longer than 50 chars...');
  dbms_output.put_line(v_str1);

  trunc_string(v_str2, 'a text longer than 30 chars, a text longer than 30 chars, a text longer than 30 chars...');
  dbms_output.put_line(v_str2);
END;
/

dbms_output:
a text longer than 50 chars, a text longer than 50
a text longer than 30 chars, a

db<>fiddle

But that's probably overkill... though that could be said about using anydata at all.