SEO

I have a decidedly ugly problem...I really want output with NO
delimiters whatsoever for specialized legacy systems reasons. Can
anyone tell me if it's possible to get mysql to return results without
the tabs? On a WindowsXP box, I am currently using:

mysql -uexport -pxxxx --force -N hh < c:\batch\getFedexFrom.sql >
P:\ship\FEDEX\fedexFROM.txt

where getFedexFrom.sql is simply

select * from hh.fedexfrom

I'd rather do it all from mysql if possible, rather than having to do a
2nd sweep with some kind of awk.

Any suggestions would be much appreciated!

Tyler Style



tyler AT
healthy habits web
DOT
com

tyler.style@gmail.com wrote:

> I have a decidedly ugly problem...I really want output with NO
> delimiters whatsoever for specialized legacy systems reasons. Can
> anyone tell me if it's possible to get mysql to return results without
> the tabs? On a WindowsXP box, I am currently using:
>
> mysql -uexport -pxxxx --force -N hh < c:\batch\getFedexFrom.sql >
> P:\ship\FEDEX\fedexFROM.txt
>
> where getFedexFrom.sql is simply
>
> select * from hh.fedexfrom
>
> I'd rather do it all from mysql if possible, rather than having to do a
> 2nd sweep with some kind of awk.
>
> Any suggestions would be much appreciated!
>
> Tyler Style
>
>
>
> tyler AT
> healthy habits web
> DOT
> com
>

select '~'||trim(A)||'~'||trim(B)||'~' as a from a;
+-------+
| A |
+-------+
| ~A~B~ |
+-------+



--
Michael Austin.
DBA Consultant

> select '~'||trim(A)||'~'||trim(B)||'~' as a from a;
> +-------+
> | A |
> +-------+
> | ~A~B~ |
> +-------+

Thank you...but it didn't work for me. It output a column of 1's. I'm
actually not sure what the pipes and tildes are supposed to doing
there, so perhaps I'm not adjusting for something I should be.

>> select '~'||trim(A)||'~'||trim(B)||'~' as a from a;
>> +-------+
>> | A |
>> +-------+
>> | ~A~B~ |
>> +-------+
>
> Thank you...but it didn't work for me. It output a column of 1's. I'm
> actually not sure what the pipes and tildes are supposed to doing
> there, so perhaps I'm not adjusting for something I should be.

Post an example of the output you want.


$ mysql test -N -B -e "select col1,col2,col3 from tab"
Value1 Value2 Value3

$ mysql test -N -B -e "select concat(col1,col2,col3) from tab"
Value1Value2Value3


Regards
Dimitre

Logos wrote:
>>select '~'||trim(A)||'~'||trim(B)||'~' as a from a;
>>+-------+
>>| A |
>>+-------+
>>| ~A~B~ |
>>+-------+
>
>
> Thank you...but it didn't work for me. It output a column of 1's. I'm
> actually not sure what the pipes and tildes are supposed to doing
> there, so perhaps I'm not adjusting for something I should be.
>

the || (double pipe) is ansi-standard to "concat" strings together. The ~
(tilde) is merely a field seperator. If omitted, then you must make sure you
data fields are well defined and ALWAYS the same length. Otherwise, when you
import it into something else, how do you where to break it?

eg. col-a int, col-b int
col-a col-b
123 45678
1234 5678

in this example, there is no way to know how many "characters" are in col-a or col-b

Using a mysql-specific function to replace the ansi-standard, you can use:

select concat('~',trim(A),'~',trim(b),'~') as a from a;
note, if the columns are integers, you can remove the trim() function - this is
used to ensure no leading/trailing spaces.

I try to use ansi-standard syntax as it can be translated from one database
engine to another fairly easy. If you use database specific functions - when
you do need to move - you must recode everything because you were not ansi-standard.


--
Michael Austin.
DBA Consultant

> 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');

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

Tyler

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. (:

--
6. I will not gloat over my enemies' predicament before killing them.
--Peter Anspach's list of things to do as an Evil Overlord

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.

That does work, thank you

I chose VARCHAR over CHAR because of this excerpt from the MySQL online
dox:
"If a given value is stored into the CHAR(4) and VARCHAR(4) columns,
the values retrieved from the columns are not always the same because
trailing spaces are removed from CHAR columns upon retrieval."

So I chose VARCHAR, and then made sure the insertion cose space-pads
the field value up to the field length.


Michael Austin wrote:

> >>When I export, what I would like to see is
> >>
> >>1234567 456

>
> 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.

On 24 Nov 2006 07:14:59 -0800, Logos wrote:
> That does work, thank you
>
> I chose VARCHAR over CHAR because of this excerpt from the MySQL online
> dox:
> "If a given value is stored into the CHAR(4) and VARCHAR(4) columns,
> the values retrieved from the columns are not always the same because
> trailing spaces are removed from CHAR columns upon retrieval."
>
> So I chose VARCHAR, and then made sure the insertion cose space-pads
> the field value up to the field length.

.... which always puzzled me as it's precisely the OPPOSITE as one would
expect from the type names (not to mention how every other RDBS I've
worked with does it). You'd thing the "VAR" one would be the one that
would strip trailing space and do it on the record write...

--
The pluses in my current job include laughing in the face of Nobel
laureates who have just lost the only copy of their data. (Hey,
I'm still a BOFH).
-- Bob Dowling