Subject [firebird-support] Re: Issues with String Truncation error on insert (FB 2.1 64 bits windows server)
Author Svein Erling Tysvær
>> Your problem is still solvable. Simply add another table. Let one table have huge field lengths
>>(CHAR/VARCHAR or BLOB SUBTYPE TEXT) and use this only for imports, then use AFTER INSERT triggers
>>to copy the first few characters of field(s) to the other table (which you in turn use for querying).
>>Variations of this solution includes adding another field rather than table or even another database
>> ('another database' would probably be an overkill and more difficult to implement in triggers).

>Do you really think that he should do this?
>i do not know what do that system
>but why you need to truncate data provided by your customer?
>Think abut this system:
>1. Customer can put an order for product and write in order info text:
>"I need 200 red boots .. but only if you can send it to me in two days"
>2. your system store truncated data
>"I need 200 red boots"
>3. and you send a request by week
>4. what do customer - say "i do not need this now, get it back"

Hi Karol!

I know nothing about his system, of course it would be ridiculous to truncate in the example you mention. Though there are other situations where it could make sense: Imagine a medical system using ICD-10 codes. Typically they would be 4 or 6 characters each. Now, suppose you received such codes in a long string from various hospitals. You know that each patient has one line and that the codes are ordered by decreasing importance. A patient can have lots of simultaneous diagnosis and they may all be important for the doctors treating the patient. However, for the statistical report that you are supposed to create from the files they send you, it is OK to only care about the five or six most important diagnoses.

Whether Fabian should truncate data or not, all depends on what kind of data he receives and what they are to be used for. The question I tried to answer was whether it was possible to truncate, not whether it was sensible in his case.

Another way that I think I've "truncated" before, is to use a trigger populated field that I can index (as you know, Firebird indexes have limited length). Though that is only to speed up searches and not a field the users ever see.