DBExpress - Auto Trim Char Fields (Using SQL Server)

Posted on 2012-08-29
Last Modified: 2012-09-13
There was an option to auto trim chars fields using dbexpress connection?

The parameter "Trim Char" or "TrimFixedChar" isn't avaiable while using SQL Server drivers.

Do you guys knows if there is any way to do that?

If not possible on the connection, is there any way to do that directly on the clientdataset? Inheriting a method? (I already use a custom ClientDataSet, but, I haven't found a method that I can perform a trim while reading records)

I suppose you guys will tell me to change char fields to varchar, but, at this moment, It's impossible to change all my database datatypes to varchar (It has almost 13 years and has more than 150gb).
Question by:hidrau
    LVL 36

    Expert Comment

    by:Geert Gruwez
    sorry ... bad database column type choice
    converting to varchar is the way to go

    besides .... you'll probably only have 30Gb in the end
    the rested is wasted space

    the other way ... create all calculated fields and trim the data
    ... but this will slllooooooowwww down your app very much
    and you will be posting all the changes back to the db
    >> the changes will be because of the trim
    and this will be overruled in the db again by the char type
    >>> back to square 1
    LVL 36

    Expert Comment

    by:Geert Gruwez
    oops i miss/crossposted 2 questions ... hasn't happened much before
    comment in other Q should have gone here:

    to make it a little more clearer
    >> if you trim the data in your delphi app, the dataset will think every row is changed

    any edit you still do and hit save, will post back the complete table to the database
    >> overwriting any simultaneous (if possible) changes by others

    i'm sorry to have to indicate that a mistake in database design costs a lot of money and time to correct
    you'll have to get the bull by the horns and dig into this
    >> any workarounds will probably be futile anyway
    LVL 25

    Expert Comment

    by:Sinisa Vuk
    Try to create custom table view or procedure in your sql db. There you can trim using sql functions and use it in app.
    LVL 36

    Accepted Solution

    didn't you forget to mention that all those views will have to made updatable using a instead of trigger on each of those views

    or all the table names in the app will have to be changed to view names

    my guess is that it's easiest to write a conversion program
    make a backup ... just in case
    >> create all tables as new_tablename
    >> cast all columns of type char to type varchar
    >> copy all the data over to the new tables with trim on char columns
    >> script all the constraints
    >> drop all the constraints
    >> drop all the oldtablenames
    >> rename the new_tablename to oldtablename
    >> recreate all the constraints from the script


    this can be done overnight
    LVL 1

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Suggested Solutions

    The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
    Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now