splitting text fields

[Steve P.]

I have to manipulate data furnished by our suppliers for input into our
business system. I have two fields that I need to populate named desc1 and
desc2 that are limited to 30 charaters each. Below are examples of data as
it comes from a file supplied by one of our suppliers:

Desc
item1 CLASSIC 99® RETRACTABLE UTILITY KNIFE
item2 EXTRA HEAVY DUTY UTILITY BLADES (5), COUNTER DISPLAY
item3 1/2" 2-EDGE HEAVY DUTY HOOK SCRAPER BLADE FITS 28-293, 28-622, 28-619

Currently I use Left([Desc],30) and Mid([Desc],31,30) functions to covert it
to this:

desc1 CLASSIC 99® RETRACTABLE UTILIT
desc2 Y KNIFE

desc1 EXTRA HEAVY DUTY UTILITY BLADE
desc2 S (5), COUNTER DISPLAY

desc1 1/2" 2-EDGE HEAVY DUTY HOOK SC
desc2 RAPER BLADE FITS 28-293, 28-62

In the last example the end of the manufaturer's description "2, 28-619" is
truncated in DESC2.

I am searching for a method to split the text so that whole words are kept
together, for example.

desc1 CLASSIC 99® RETRACTABLE
desc2 UTILITY KNIFE

desc1 EXTRA HEAVY DUTY UTILITY
desc2 BLADES (5), COUNTER DISPLAY

desc1 1/2" 2-EDGE HEAVY DUTY HOOK
desc2 SCRAPER BLADE FITS 28-293,

In the last example desc2 is still truncated which is okay *but* if it were
possible to do the following it would be the icing on the cake:

desc1 1/2" 2-EDGE HEAVY DUTY HOOK
desc2 SCRAPER BLADE FITS...

In case it's not apparent, what I am asking for is that when the desc2 field
is truncated I would like to cut it off at the last whole word that would
fit within 27 characters and then append 3 periods to the text to indicate
to users that the field was indeed truncated.

Your help is appreciated.

Steve P.

 

[CJ]

Steve,

My first reaction to your post was that you redesign your
target database to accept descriptions of a length that
will contain the data that you get from your suppliers -
otherwise you are throwing away, possibly vital, data).
Imagine a case where the last part of the string
(discarded) contains a safety warning! This idea is
probably a non starter as you will no doubt have lots of
existing functionality based on the two 30 char string
fields.

Another alternative is to ask your suppliers to do the
edit for you so that no, again possibly vital, data is
lost. Again this idea is probably a non starter

What you want to do can be done by checking each single
character in the incoming string in turn (using the Mid
function) and acting according to the position and value
of that character. The real crunch bit of the checking
needs to be done towards (and beyond) the end of your
Desc2 field. Read the incoming string before and beyond
the last character your database can accept and use the
single character value to determine your action. For
example:

desc1 1/2" 2-EDGE HEAVY DUTY HOOK
desc2 SCRAPER BLADE FITS 28-293, 28-622, 28-619

In Desc2 "28-622 etc" is truncated to "2" but by reading
beyond that you can establish that the next character is
an "8" (i.e. not a space or punctuation) so you know that
you are truncating the data. What you then need to do is
to backtrack through the string (again Mid Function)
until you find the point at which it is logical to
terminate the data and then append "...". Exactly how
you do this would be down to data content - e.g. you
could choose to ignore numbers and backtrack to the last
alpha character resulting in:

desc2 SCRAPER BLADE FITS...

or you could just base this on spaces or punctuation
resulting in:

desc2 SCRAPER BLADE FITS 28-293,...

or

desc2 SCRAPER BLADE FITS 28-293...

Hope this is of some little help

CJ

-----Original Message-----
I have to manipulate data furnished by our suppliers for input into our
business system. I have two fields that I need to populate named desc1 and
desc2 that are limited to 30 charaters each. Below are examples of data as
it comes from a file supplied by one of our suppliers:

Desc
item1 CLASSIC 99® RETRACTABLE UTILITY KNIFE
item2 EXTRA HEAVY DUTY UTILITY BLADES (5), COUNTER DISPLAY
item3 1/2" 2-EDGE HEAVY DUTY HOOK SCRAPER BLADE FITS 28- 293, 28-622, 28-619

Currently I use Left([Desc],30) and Mid([Desc],31,30) functions to covert it
to this:

desc1 CLASSIC 99® RETRACTABLE UTILIT
desc2 Y KNIFE

desc1 EXTRA HEAVY DUTY UTILITY BLADE
desc2 S (5), COUNTER DISPLAY

desc1 1/2" 2-EDGE HEAVY DUTY HOOK SC
desc2 RAPER BLADE FITS 28-293, 28-62

In the last example the end of the manufaturer's description "2, 28-619" is
truncated in DESC2.

I am searching for a method to split the text so that whole words are kept
together, for example.

desc1 CLASSIC 99® RETRACTABLE
desc2 UTILITY KNIFE

desc1 EXTRA HEAVY DUTY UTILITY
desc2 BLADES (5), COUNTER DISPLAY

desc1 1/2" 2-EDGE HEAVY DUTY HOOK
desc2 SCRAPER BLADE FITS 28-293,

In the last example desc2 is still truncated which is okay *but* if it were
possible to do the following it would be the icing on the cake:

desc1 1/2" 2-EDGE HEAVY DUTY HOOK
desc2 SCRAPER BLADE FITS...

In case it's not apparent, what I am asking for is that when the desc2 field
is truncated I would like to cut it off at the last whole word that would
fit within 27 characters and then append 3 periods to the text to indicate
to users that the field was indeed truncated.

Your help is appreciated.

Steve P.





.