Peter H. Coffin wrote:

> On 13 Nov 2006 16:12:35 -0800, Logos wrote:
>
>>>Post an example of the output you want.
>>
>>All fields are varchars, and are space padded on insert to fill them
>>out to the maximum for each field.
>> (eg) col1 = VARCHAR(10), col2 = VARCHAR(6)
>> (eg) INSERT INTO sample VALUES('1234567 ',' 456');
>
>
> Why even bother with varchar then? A simple CHAR(10) would do what you want
> with that regard, wouldn't it?
>
>
>>When I export, what I would like to see is
>>
>>1234567 456
>>
>>This is because the legacy app (called filePro) allocates a fixed
>>number of bytes per record, and then a fixed number of bytes per field;
>>it then pads with spaces as necessary for each field in a flat file.
>>
>>I actually solved this by simply redefining the schema in filePro as
>>having a 1 byte field between every field to accommodate the tabs, but
>>this is both inelegant and a pain in the ass to define So I'd be
>>interested in a good solution on mysql's side.
>>
>>Concat looks like it would work, but how CPU-intensive is it? I would
>>like an efficient solution if at all possible...tho I doubt there is
>>one, as I saw that the ability to define field delimiters was on the
>>wish list
>
>
> concat() will work nicely, especially if you alter the table to use
> CHAR. It's not particularly CPU-intensive, and whatever time it adds to
> your export will be well-used put to figuring out how to eliminate
> FilePro entirely. (:
>


Since your data is already in varchar (does not space-fill) then you could use
the ansi-standard CAST function.

select cast(col1 as CHAR(20))||cast(col1 as CHAR(10))... from table1;

again the || (double pipe) is ansi-standard concat syntax. I prefer to keep my
sql and functions ansi-standard as you never know when you are going to need to
port to a real database engine.

the CHAR datatype should space-fill to the length specified. Last night I
discovered on my platform (OpenVMS) that CHAR was not space-filing so a bug
report was entered.

the above should result in something like:

select '~'||cast('ABC' as char(10))||'~';

~ABC ~

select '~'||'ABC'||'~';
~ABC~

[NOTE: the ~ is concatenated so you can see the "whitespace"]

--
Michael Austin.