Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    getting rid of trailing spaces in a table column

    I have a demand to eliminate trailing spaces in a table column. The column is of varchar datatype, so I imagine that doing an export to a flatfile and re-importing will automatically get rid of trailing blank after the data ????????
    Can anyone think of an alternative approach ???
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: getting rid of trailing spaces in a table column

    Ever thought on using standard functions? {L/R}TRIM() removes leading and trailing spaces. So, all you have to do is:

    UPDATE <YourTable>
    SET <YourColumn> = rtrim(<YourColumn>)
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Oeps,

    My guess was that it was varchar datatype . I was wrong , it is of type 'text' (I am an DB2 / ORACLE guy, so I expected other field type)
    The RTRIM function does not seem to work with 'text' datatype......
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    If you did not expect the datatype TEXT, are you sure that it has to be TEXT? Consider changing your structure!
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  5. #5
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    The SQL database is part of an application we bought. I am afraid that changing the structure will invalidate support on the whole package. The column contains names of picture files. Is there a specific reason to go for a text field in that case???
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  6. #6
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    You mean the path to the picture files? I suspect that the application designer expected paths longer than 255 characters, which is in some DBMS the limit for a VARCHAR datatype. However, SQL Server allows 4000 characters, which should be sufficient. So, you may refine your update statement as follows:

    UPDATE <YourTable>
    SET <YourColumn> = rtrim( cast(<YourColumn> AS NVarChar(4000)) )
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  7. #7
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Hey doctor,

    I did what suggested in your last post, but used the convert() function instead. Nice this T-SQL stuff, feels like eating a different kind of food..
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  8. #8
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    See Simple Question About Data Type for a discussion about CAST() and CONVERT(). Disregard the last few messages as they are irrelevant for the thread.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •