LTRIM/RTRIM Problem

[dwearden]

I have a stored procedure the last part of which has a select statement. I am running an isql batch file to extract the results to a csv file.

For some reason when I select on the varchar fields using RTRIM(LTRIM(field.name)), I still get all the leading or trailing spaces.

Is there something I'm missing ?

Thanks in advance

Dave

 

[vongrunt]

AFAIK trim functions don't remove tabs (ASCII 9). Maybe that's the problem.

 

[dwearden]

When I open up the text file they are definately spaces, not tabs I'm afraid.

 

[vongrunt]

Ack... I'm not sure isql (or osql, whatever) supports variable-width output columns.

 

[ESquared]

The default behavior in SQL server is for fields to compare as equal even with trailing blanks (but not for leading blanks). Is it possible the problem is because of this?

For example,
'' = ' '
'frog ' = 'frog'
' abcde ' = ' abcde '

but
' xyz' <> ' xyz'


-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[dwearden]

The select statement works, it's not the criteria that is the problem. It just won't trim down the fields and get rid of the spaces. I'm in the UK so at home now, but I'll post the select statement up tomorrow.

 

[ESquared]

Are the columns char or varchar? Have you tried converting to varchar?

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[dwearden]

This is as simple as it gets. All fields are varchar but it still won't trim them down ??

SELECT
RTRIM(LTRIM(customer.custname)),
RTRIM(LTRIM(customer.custcode)),
RTRIM(LTRIM(customer.job))
FROM
customer

 

[ESquared]

What program are you using to select these? Have you tried different methods, such as QA or EM?

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[NickW2]

bcp can be used instead of isql. The file output will exactly match the results of the query.