Removing trailing spaces in a table column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Removing trailing spaces in a table column

Removing trailing spaces in a table column

Rate Topic

Display Mode

Topic Options

Author

Message

Jayanth_Kurup

Posted Tuesday, January 22, 2008 2:43 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:43 AM
Points: 1,785, Visits: 1,008

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
Smile

Jayanth Kurup

Post #445783

vyas

Posted Tuesday, January 22, 2008 2:53 AM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:02 AM
Points: 3,131, Visits: 1,056

Check the following link may be useful

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

Post #445788

SwePeso

Posted Tuesday, January 22, 2008 2:56 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:26 PM
Points: 2,359, Visits: 3,293

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

Jayanth_Kurup

Posted Tuesday, January 22, 2008 4:20 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:43 AM
Points: 1,785, Visits: 1,008

Hi ,

Fixed the issue Smile

, 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

bwieland

Posted Tuesday, June 14, 2011 9:44 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 14, 2011 9:41 AM
Points: 7, Visits: 20

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

SwePeso

Posted Tuesday, June 14, 2011 10:05 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:26 PM
Points: 2,359, Visits: 3,293

SELECT ASCII(SUBSTRING(Col1, n, 1))

N 56°04'39.16"
E 12°55'05.25"

Post #1125149

piyush.varma

Posted Wednesday, October 03, 2012 12:49 PM

Forum 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 »

Permissions