CODEWITHSUNDEEP
sqlsql-servertrimvarbinarymaxvarcharmax
Matt Weick
Matt Weick

Reputation: 332

RTRIM not working as expected

I am facing a strange issue where RTRIM which I have used thousands of times is not working as expected. It's almost like there are characters at the end that look like spaces but SQL 2008 is not treating them like spaces, therefore the "spaces" at the end are not getting removed. The number of "spaces" varies per row. Anyone have any thoughts or encounter this before?

The cursor below selects records while casting the content as varchar(max). Then it's supposed to update the records based on content key while shaving off trailing characters and casting back as varbinary(max). Here is my cursor:

declare @att_id int
declare @content_key nvarchar(50)
declare @content_char varchar(max)
declare @content varbinary(max)

declare cur cursor for
      select a.att_id, b.content_key, CAST(b.content as varchar(max)) as Content from saattachment as a
            join digitalassetcontent as b on b.content_key = a.content_key
                  where a.att_name like '%^^Product Description%'

open cur

Fetch next from cur into 
      @att_id,
      @content_key,
      @content_char

While (@@FETCH_STATUS=0)

Begin
      set @content = (select CAST(RTRIM(@content_char) AS VARBINARY(MAX)))
      update digitalassetcontent set version = version + 1, content = @content where content_key = @content_key
      update i18ndigitalassetcontent set version = version + 1,content = @content where content_key = @content_key
      update saattachment set version = version + 1, att_updated=GETDATE() where att_id = @att_id

Fetch next from cur into
      @att_id,
      @content_key,
      @content_char

end

close cur
deallocate cur

UPDATE:

Example from digitalassetcontent.conent: 0x457874656368203430304120414320636C616D70206D657465722C2041432063757272656E74206D6F64656C20746F206D65657420796F7572206170706C69636174696F6E206E656564732E20203230303020636F756E74204C434420646973706C61792E20204869676820616363757261637920666F722063757272656E74206D6561737572656D656E74732E2020302E3922202832336D6D29206A61772073697A65206163636F6D6D6F646174657320636F6E647563746F727320757020746F203330304D434D2E2020436F6E74696E756974792062656570657220616E642064696F646520746573742E20204461746120686F6C6420616E64206D617820686F6C642E20204F7665726C6F61642070726F74656374696F6E20666F7220616C6C2072616E6765732E20204F76657272616E676520616E64206C6F77206261747465727920696E64696361746F72732E20204175746F72616E67696E672077697468206175746F20706F776572206F66662E0D0A090909090909090909090D0A090909090909090909090D0A090909090909090909090D0A09090909090909090909

Upvotes: 0

Views: 406

Answers (0)

Related Questions