Re: Does somebody know a better way

In article <20021018073034.15182.00002161_at_mb-fy.aol.com>, noodles_at_aol.com says...
> Chris,
>
> Is this for an OLTP system?

Hybrid - we have various batch feeds going in and out as well as intermittent selects/updates from users. Also Business Objects universe for users to do adhoc  queries.
>
> I can't help but think using the client to remove the bad chars BEFORE getting
> in the database would be easier and solve the systemic problem of non-alpha
> numeric chars. This might be a classic case of GIGO and the database being
> expected to exclude all of the user's garbage.
>

This particular set of data is uploaded through an EDI gateway - hundreds of thousands of records per day inserted - I have no access to the data before I have to extract it for this particular feed I am concerned about, and one of the requirements of the target system is that non-alphanumerics are stripped from certain fields. Not always the case - especially data reported from Spain and Portugal ... personal vendetta being outed here :-)

> Obviously your DB has all of the excess baggage of these chars that you
> possibly wont ever want and it's sort of like logical fragmentation with a
> possibly poor data density.
>

No - in 99% of cases we want the extra data - this stripping of the non- alphanumerics is an exception to the normal operation of the system

> BUT,... this might not be feasible in your specific situation.
>
> Running a SQL solution with a translate function might not reach your
> performance requirements. Maybe the TRUE SQL gurus on here have a better
> solution...
>

That's what I hoped. I can easily write some PL/SQL to do the job but would prefer creative use of an inbuilt function if possible. Its all working at the moment using the 'translate' based solution in my original post, performance is almost acceptable, but I would like the solution to be more bug-proof by building an 'include characters' mechanism rather than the 'exclude characters' mechanism I have devised. I might just resort to writing an awk or sed script to post-process the file, but I am the only Unix guy in the house and thus the code is less maintainable if I do this.

> Sometimes the solution is process-based and not code-based.
>
> My 2 cents,
> Cliff
>

Thanks for your input Cliff (ching, 2c duly pocketed) Received on Fri Oct 18 2002 - 12:12:06 CDT