0

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

Matt Weick
  • 332
  • 6
  • 19
  • find out what that "space" character is, and use Replace() method, or if you only want to remove trailing character, write your own RTRIM() function – NeedAnswers Jun 30 '14 at 13:55
  • can you write short example that narrows down the problem? – Mitch Wheat Jun 30 '14 at 13:56
  • 1
    Can you show the relevant `digitalassetcontent.content` value(s), e.g. in hexadecimal? – JimmyB Jun 30 '14 at 13:57
  • I changed the title accordingly, thanks for heads up. @hoangnnm - I tried doing a replace but I don't know what the "space" character is because it doesn't display in SQL. – Matt Weick Jun 30 '14 at 13:57
  • @HannoBinder - I have updated my post to include an example from digitalassetcontent.content in hexadecimal – Matt Weick Jun 30 '14 at 14:00
  • try this to get its ascii : select ascii(right('s', 1)) , or unicode : select unicode(right('s', 1)) , blank character(space() ) is 32 – NeedAnswers Jun 30 '14 at 14:02
  • There's a whole list of hidden characters to account for that RTRIM doesn't clear. See this: http://stackoverflow.com/questions/8655909/whats-the-best-way-to-identify-hidden-characters-in-the-result-of-a-query-in-sq – Matt Jun 30 '14 at 14:03
  • 1
    On a note: 0x0d is Carriage Return, 0x0a is Line Feed, and 0x09 is Tab; no actual whitespace (0x20) there. – JimmyB Jun 30 '14 at 15:01
  • I've confirmed it's definitely the '0D0A09090909090909090909' binary string at the end causing this. I've never done a REPLACE on a varbinary string. I tried CAST(REPLACE(content, '0D0A09090909090909090909', '') as VARBINARY(MAX)) and this did not work. – Matt Weick Jun 30 '14 at 15:50
  • 1
    I have created a new thread because I feel the topic is different now that we have isolated problem. I'm relatively new to the forum, was this the right thing to do? [Click Here for New Thread Link](http://stackoverflow.com/questions/24494778/how-to-perform-a-replace-on-varbinary-data-in-sql) – Matt Weick Jun 30 '14 at 16:31
  • I wish more people would stick to clearly separating their questions like you do! :) – JimmyB Jul 02 '14 at 09:25

0 Answers0