0

I am using substring to remove last character, but not sure what went wrong but it wont remove last character when the input string in long, however it removes last character for shorter string . Below is what I am using SUBSTRING(@XXX,0,(Len(@XXX)-1))

when Len(@XXX) is small it is removing last character but when length of input is 98015 it is not removing. Is there any limit ? what is wrong over here?

09999
  • 43
  • 2
  • 8
  • How are you declaring the search string? Wondering if you may be working with double-byte character set data.. – David W May 02 '12 at 19:23
  • What database are you using? If you are using SQL Server Management Studio and are looking at the string returned there, then the maximum is about 43 Kbytes. You wouldn't see if the last character is being replaced. – Gordon Linoff May 02 '12 at 19:28

1 Answers1

0

I've just tested it and it seems fine, leading me to think that the issue is with how you examine the result. This works fine in Query Analyzer (assuming you're using MS SQL), the below script generates a string 100,000 chars long (of '-' chars with an ending '0' char) and then checks what is the last character.

declare @longstring varchar(max)
declare @lastchar varchar(1)
declare @i int

set @longstring = ''
set @lastchar = ''
set @i = 1
while @i <= 100000
begin
    set @longstring = @longstring + '-'
    set @i = @i + 1
end
set @longstring = @longstring + '0'
print 'Original long string: ' + @longstring
set @lastchar = SUBSTRING(@longstring, LEN(@longstring), 1)
print 'Last character before substring: ' + @lastchar
--Last character before substring: 0
set @longstring = SUBSTRING(@longstring, 0, len(@longstring) - 1)
set @lastchar = SUBSTRING(@longstring, LEN(@longstring), 1)
print 'Long string after substring: ' + @longstring
print 'Last character after substring: ' + @lastchar
--Last character after substring: -
itayw
  • 614
  • 9
  • 20
  • Thanks for the code. this helped me to understand where I went wrong. The problem is managment studio output as it dint show all the output i thought last character is not getting trimmed. – 09999 May 02 '12 at 20:44
  • there's a long thread about the query analyzer behavior here: http://stackoverflow.com/questions/952247/sql-server-truncation-and-8192-limitation if you're interested. In any case, with a long string, you'll probably need to move away from QA – itayw May 02 '12 at 20:52