Conversion from Btrieve

[rwies]

I am working on a conversion that starts with a btrieve database. The author of the original database has written a program to convert the data to an Access database which I am importing into my SQL database. The problem I have is that all fields are padded with char(0). If a field has a length of 50 and only 10 characters are actually used the remaining 40 are char(0)(ascii zero). I need to strip these from the data base. Any ideas? There are about 70 tables and about 1000 fields. Thanks

 

[dbomrrsm]

before we go any further can we clarify a few things.

if the data was abcde it would be padded with 45 O's if the field length was 50 ?

can you have the situation where the data is actually 12340 so again this would be padded with 45 0,s if the filed length was 50 - what I am getting at there is can you guarantee that the actual data will never have a 0 or combination of 0's the the right of the actual data ?

[blue]DBomrrsm[/blue]

 

[donutman]

I would rewrite the conversion program! Then I'd bring up the original programmer on charges of gross incompetence and recommend that the prosecutor ask for capital punishment. Of course, he'd probably be acquitted on grounds of permanent insanity and as proof of his condition he'd probably show that he programmed it in assembler language...why else would he be messing with ASCII zeros?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[rwies]

The character used to pad the field does not appear (looks like a space). I used the following transact sql to reveal the ascii character value of what existed:

select AccountNumber, PremiseZip, len(PremiseZip) as len,
ascii(right(PremiseZip,3))as [???]

As you can see, I am examing a zip code field. If the field contains a 5 digit zip code the length is 10, the 5 digits plus 5 ascii code zero charaters. The value in the ??? column is 0. If the field contains a zip + 4 zip code (12345-1234) the length, as expected is 10 and the value of the ??? column would be 50. Decimal 50 = Ascii Char 2.

When I click in a field (query view), a series small squares appear as the padding. Nothing appears until I click in the field.

The original database data is from a DOS based Btrieve database.

Thanks for your assistance.

 

[rwies]

You are right, the original program is written in assembler. I agree, he should be shot for not bringing the program up to date and moving the trash to the Access database.