SQLTeam.com | Weblogs | Forums

Rtrim doesn't seem to be working (I'm sure it's me)


#1

Hi... I'm trying to clean up some data in my table, and am finding that my script to remove duplicates isn't working right. It looks to me that there are spaces at the end of each string in one of my columns, which I'm not able to remove. I've tried using rtrim and replace, but to no avail. For example:

SELECT REPLACE(SUID,' ',''), "SUID" FROM news_Indexing.dbo.gsstatus;

where the column SUID displays 31 characters instead of the 12 which are visible alpha numerics.

The other odd thing is, the first row in the 169,000 table does NOT have the spaces at the end of each string.
Can somebody please advise?

Thank you for any help!


#2

It could be one of two things:

  1. Check the data type of the column SUID. If it is NCHAR(n) or CHAR(n) (rather than NVARCHAR(n) or VARCHAR(n) even if you update the column after trimming the trailing spaces it would still have trailing spaces. However, in your case, I think this may not be the cause because you are doing only a select, and also, there are some rows for which there are no trailing spaces (assuming the length of the data in those rows is less than the declared length of the column).

  2. The more likely reason is that the trailing spaces are not really spaces, but some other invisible characters. You can test if that is the case by casting the column to binary. For example,

    SELECT CAST(SUID AS VARBINARY(MAX)) FROM news_Indexing.dbo.gsstatus;

Now look at the trailing characters. If they are spaces they should be 20. Example:

SELECT CAST('abc  ' AS VARBINARY(MAX));

#3

James - thank you so much. I'm going to hang onto those to use again. Unfortunately, tried to modify and execute some other code I found and pretty much overwrote the field in question. 169,000 times. Ugh.

Luckily I realize that I'm clumsy, awkward, and not so bright, so I downloaded to excel first, then just used vba to clean it up and reloaded it to the db. Issue solved (for now).

Thank you again for your time. I'm going to look up each of those functions and see if I can get a better understanding.
Have a great day....


#4

Been there, done that, have the t-shirt. Backups!