Constraint to trim data (column level)

Posted in 2013

Topics: Triggers, Constraints & Referential Integrity

PRIYANKA MOKHADKAR — Fri, 14 Jun 2013 04:14:14 — source: IIUG Forums & Mailing Lists

Hello all, I am dealing with Unicode Data, where my entry always ends with a space.now what i want to do is trim that space at column level by means of constraints or something else, but as far as i know we cant use function in check constraints. where i tried using TRIM function. So is there any way to get rid of that space. Please help here.

↪ replying to PRIYANKA MOKHADKAR

Art Kagel — Fri, 14 Jun 2013 04:36:08 — source: IIUG Forums & Mailing Lists

Just use an insert and an update trigger and patch up the column in the trigger or in a stored procedure called from the trigger. BTW, if the column is a CHAR type it will always be space padded to its full length! Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Jun 14, 2013 at 12:14 AM, PRIYANKA MOKHADKAR < priyanka@avantisoftware.in> wrote: > Hello all, > > I am dealing with Unicode Data, where my entry always ends with a > space.now what i want to do is trim that space at column level by means of > constraints or something else, > > but as far as i know we cant use function in check constraints. where i > tried using TRIM function. > > So is there any way to get rid of that space. Please help here. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c374205a471704df15c8b7

↪ replying to PRIYANKA MOKHADKAR

Jonathan Leffler — Fri, 14 Jun 2013 05:44:57 — source: IIUG Forums & Mailing Lists

If the column type is CHAR, the system adds spaces and there's nothing you can do about it. If the column type is VARCHAR, the system should not add spaces as long as you are careful to claim that host variables are VARCHAR and not CHAR. If you use CHAR host variables, the system will blank pad them (and then not trim the blanks from the VARCHAR storage, partly because its two different bits of the system doing the padding and trimming). If you don't want the host variables blank padded, don't use CHAR types. On Thu, Jun 13, 2013 at 9:14 PM, PRIYANKA MOKHADKAR < priyanka@avantisoftware.in> wrote: > Hello all, > > I am dealing with Unicode Data, where my entry always ends with a > space.now what i want to do is trim that space at column level by means of > constraints or something else, > > but as far as i know we cant use function in check constraints. where i > tried using TRIM function. > > So is there any way to get rid of that space. Please help here. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2013.0521 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --001a1132f84870e9a304df16be55