Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tracy5436
Yak Posting Veteran
50 Posts |
 Posted - 2008-06-04 : 17:16:45
|
| I have created a query which inserts data into a table. The table has fixed length fields, but the length of the data in the fields varies. I have setup a dts package which exports the data from the table into a comma delimited text file. The problem is that I need the data in the fields trimmed when it is exported to the text file. I tried using the trim function in the query to do this, but of course since the lengt of the field is fixed, this does not make a difference. I have also tried to use the query tool in dts to trim the fields but this causes th fields not to appear in the text file. Is there any way to do this ? |
|
|
dshelton
Yak Posting Veteran
73 Posts |
Posted - 2008-06-06 : 02:07:03
|
| RTRIM(CAST(Field AS VARCHAR)) |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-06 : 02:18:00
|
quote:
Originally posted by dshelton
RTRIM(CAST(Field AS VARCHAR(length)))
Always specify a length when casting to varcharhttp://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-06 : 02:18:44
|
quote:
Originally posted by tracy5436
I have created a query which inserts data into a table. The table has fixed length fields, but the length of the data in the fields varies. I have setup a dts package which exports the data from the table into a comma delimited text file. The problem is that I need the data in the fields trimmed when it is exported to the text file. I tried using the trim function in the query to do this, but of course since the lengt of the field is fixed, this does not make a difference. I have also tried to use the query tool in dts to trim the fields but this causes th fields not to appear in the text file. Is there any way to do this ?
Didnt understand what exactly is your scenario. Can you provide us some data sample? |
|
|
|
mayoorsubbu
Yak Posting Veteran
95 Posts |
Posted - 2008-06-11 : 00:56:05
|
| Try, LTRIM(RTRIM(CAST(Field AS VARCHAR(length)))) this will remove all trailing as well as leading blanks |
|
|
|
|
|
|
Trimming Blank spaces