trim space from text datatype using osql

Question

trim space from text datatype using osql

mike bossart

SSC Veteran

Points: 201
More actions
January 19, 2005 at 2:23 pm

#59892

here's my situation
I have a text datatype field that I need to select into a file. When I do this in osql it put a space before my data and a lot of spaces after the data. I've tried CONVERT and CAST then using RTRIM and LTRIM, but that doesn't seem to take the spaces off.
here's what I have
Using CAST
osql -U uname -P pword -S server -d database -h-1 -w 16384 -a 16384 -n -Q "select RTRIM(LTRIM(CAST(MsgText as varchar(8000)))) from dbo.tablename where fname = 'field_val'" -o D:\DB_msgs\field_val.dat
Using CONVERT
osql -U uname -P pword -S server -d database -h-1 -w 16384 -a 16384 -n -Q "select RTRIM(LTRIM(CONVERT(varchar(8000), MsgText))) from dbo.tablename where fname = 'field_val'" -o D:\DB_msgs\field_val.dat
Any help would be appreciated

Mike

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246961 More actions · Answer 1

Not familiar with osql, but a couple of ideas ...

It sounds like osql is adding the spaces - I'm assuming that they do not exist in the raw data? If so, I guess it's not surprising that trimming and casting are failing - as these functions are working on the raw data.

Perhaps you could try creating a view containing the MsgText field already cast as varchar(8000) and see whether this makes a difference.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 2

That is how it works on osql or isql. There is nothing much you can do, at least I just deal with it and don't read the first character.

If you later bcp this data back into a database, you can trim it with ltrim. But it is is a char(13), you have to replace it with ''.

mom

mike bossart SSC Veteran Points: 201 More actions · Answer 3

yeah, I gave up trying to accomplish this in osql, I just wrote a quick PERL script and trimed things up nicely.

SteffiDBA SSC Veteran Points: 233 More actions · Answer 4

Would you post your perl script that you used to do the trim. I am running into the same situation for an upgrade that I will be doing in the near future and it sounds like you have a perfect solution to my problem.

Thx

mike bossart SSC Veteran Points: 201 More actions · Answer 5

here's the perl script...it takes a paramenter of the filename the records are saved to and saves it as "Clean_" + filename.

#!/usr/bin/perl -w

my $file = $ARGV[0];

open (MYFILE, $file) || die "couldn't open the file!";

while ($record = <MYFILE> ) {

$record =~ s/^\s+|\s+$//g;

open (NEWFILE, ">>Clean_".$file) || die "couldn't open the file!";

print NEWFILE $record;

print NEWFILE "\r\n";

close(NEWFILE);

}

close(MYFILE);

Enjoy,

Mike