Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Removing trailing spaces in a table column Expand / Collapse
Author
Message
Posted Tuesday, January 22, 2008 2:43 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, June 12, 2014 2:04 PM
Points: 1,789, Visits: 1,014
Hi

I am having trouble removing trailing spaces in a table column (query phrase).

so far i have tried using Ltrim , rtrim but no luck , i have used len and substring as well . funny thing is when using len(queryphrase) its counting the trailing space.

also an update with replace dosen't work

I have set the table with ansi padding off and have used both nvarchar and char columns to see if it made any difference , but no luck there either.

Are there any other options available ???

Thanks in advance




Jayanth Kurup
Post #445783
Posted Tuesday, January 22, 2008 2:53 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, September 30, 2013 2:09 AM
Points: 3,131, Visits: 1,058
Check the following link may be useful

http://www.thescripts.com/forum/thread162850.html



Post #445788
Posted Tuesday, January 22, 2008 2:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:35 PM
Points: 2,393, Visits: 3,399
LEN() function is NOT counting trailing spaces.
DATALENGTH() does.

Are you the spaces really are spaces (ascii value 32)?
Make sure they are not hard-spaces (ascii value 160).



N 56°04'39.16"
E 12°55'05.25"
Post #445791
Posted Tuesday, January 22, 2008 4:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, June 12, 2014 2:04 PM
Points: 1,789, Visits: 1,014
Hi ,

Fixed the issue , the trailing space was in fact a carriage return , ascci value (13) , used a replace(queryphrase,char(13),'') to fix the issue .

Guess where i went wrong was assuming there could only be spaces in the column.

Jay


Jayanth Kurup
Post #445819
Posted Tuesday, June 14, 2011 9:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 8:11 AM
Points: 7, Visits: 21
Can you advise as to how you fixed the issue and how you knew it was a CR/LF. We are having the same issue when loading mainframe data into our systems and an RTRIM is not working. Thanks.
Post #1125122
Posted Tuesday, June 14, 2011 10:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:35 PM
Points: 2,393, Visits: 3,399
SELECT ASCII(SUBSTRING(Col1, n, 1))



N 56°04'39.16"
E 12°55'05.25"
Post #1125149
Posted Wednesday, October 3, 2012 12:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 21, 2012 12:59 PM
Points: 8, Visits: 19
I used following to remove only trailing white spaces in a CHAR(30) column:

RTRIM(LTRIM(CAST(City_Id As VARCHAR(30)))) AS City_Id

Piyush Varma
Post #1367970
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse