query to split fields into multiple rows?

[bu]

I have a database field that represents an item description and varying
length. I am using the database to import the item description into another
program (the other program is ODBC-aware ). The problem is that the
description would be imported into the other program in lengths of 50
characters. So if the entire description is 160 chars, the program would
need the description in 4 rows - 3 rows of 50 and 1 row of 10.

I have been trying to determine a way to write a query that would
essentially parse the description field into lengths of 50 chars AND return
each parsed chunk as a different row. Needless to say, I am stumped.

If anyone has any ideas or suggestions, it would be greatly appreciated!

thanks in advance,
bill

 

[Guest]

Try the MID Function. You may need the LEN Function also to determine the
overall length.

 

[bu]

thanks!

Try the MID Function. You may need the LEN Function also to determine the
overall length.

 

[John Spencer (MVP)]

One way that might work would be to add another table to your database that had
integers in it from 1 to N (where N is the maximum number of segments you would
see in the description field). Then you might be able to use something like the
following UNTESTED SQL.

SELECT A.FieldA, A.FieldB,
MID([Description,(B.Numbers-1)*50+1,50) as ChoppedLiver
FROM YourTable As A, NumbersTable as B
WHERE Len(MID([Description,(B.Numbers-1)*50+1,50) & "") > 0

If that works then you could turn it into an append query.

Of course, you could shorten all that by making your numbers table only hold the
starting numbers of 1,51,101,151,.... then you could use
MID(A.Description,B.Numbers,50) as ChoppedLiver
as the calculated field

As I said, this is just an idea. I have not tested it and don't intend to test it.