I'm producing a file every couple of minutes that needs to be processed by a very old (1984) SunOS machine. The file has to be presented in a flat format where individual fields need to be padded out to a particular length. I've had a couple of problems with this which I've solved, but the big one at the moment is values that are NULL, I'm getting the whole field reduced to \N instead of being padded out.
Another problem is that when I force in a NULL value I'm getting the result padded out further than I should:
SET @sql_text =
CAST(LPAD(4, 2, '0') AS UNSIGNED)
, CAST(LPAD(12345, 6, '0') AS UNSIGNED)
, RPAD('W1',3,' ')
, RPAD(NULL, 3, ' ')
, CAST(RPAD(1, 1, '1') AS UNSIGNED)
into outfile 'TEST-"
, DATE_FORMAT( NOW(), '%Y%m%d%H%i%s')
, ".txt' FIELDS TERMINATED BY '' LINES TERMINATED BY '\n';"
PREPARE s1 FROM @sql_text;
DROP PREPARE s1;
(The bottom line is to demonstrate where I would expect values to be)
4 12345 W1 1
Can anyone help? Do I need to put an IF/ELSE statement on every line that could be a NULL value in the SELECT part of the query?
By the way, the reason that a lot of those values are CAST is because I kept getting hex values returned that I thought were causing other problems so I was just being thorough.
MySQL v5.1.30 by the way