1

I'm trying to use concat on some variables. These variables have been declared as chars:

declare v_order_date char(10);
declare v_quantity char(11);
declare v_plant char(100);

I have a cursor where I am setting these as some outputs. To call the results, I am setting v_msg as

set v_msg := concat( v_msg, '\n', v_order_date, v_quantity, v_plant);

However, the result I am getting when I select v_msg is:

2012-01-222501008 Creeping Buttercup

but I want to maintain the length of the declared variables in my select that looks like this

2012-01-22  250    1008    Creeping Buttercup

Are there any suggestions? Thank you.

Matt Ball
  • 354,903
  • 100
  • 647
  • 710
user1682055
  • 93
  • 2
  • 3
  • 9
  • Do not confuse maximum column length for some sort of "variable length." You want to pad each value to the maximum column length before concatenating. In a language with `printf` you could do this with a "simple" format specifier; I'm not sure of the idiomatic way to do this with SQL. – Matt Ball Oct 04 '12 at 03:20

1 Answers1

0

Use MySQL repeat() and char_length()

set v_msg := concat(v_msg, '\n', v_order_date, repeat(' ', 10-char_length(v_order_date)), '\n',  v_quantity, repeat(' ', 11-char_length(v_quantity)), '\n' , v_plant);
gtgaxiola
  • 9,241
  • 5
  • 42
  • 64