If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > getting rid of trailing spaces in a table column

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On