Sql-server – Space at the end of string

sql server

I have a SQL code like shown below:

declare @str nvarchar(max),@i int
set @i=0
set @str='abc '
declare @tbl Table(a  nvarchar(max)) 
insert @tbl select @str
while (select a from @tbl)<>''
begin 
    set @i=@i+1
    set @str = substring(@str,2,len(@str))
    update  @tbl set a=@str
select * from @tbl
end

Here @str has value 'abc ' (there is a space at the end). When above query is executed it will stop when only a space is present in 'a'.
Also output of this query is:

bc
c
<here blank>

For the above query if I give input @str as 'abcd'
then output will be

bcd
cd
d
<here blank>

So in the first case that is @str='abc ' I want to get output like

bc
c
<here blank>
<here blank>

Now the code is checking for space and because of that I am having problem. But I want it to consider the space at the end also.

Same is the problem in SQL for len() also. Both len('a') and len('a ') will return 1.
So if anyone please help on my query so that it will give my desired output.

Best Answer

When checking if a string is empty or not, append a non-space character to it and compare the result to the same character. This

@string + '.' <> '.'

will evaluate to TRUE only when the @string is truly empty. The @string's trailing spaces will count (because they will no longer be trailing after appending the .).

And you can go similarly about finding the length. This

LEN(@string + '.')

will only ever evaluate to 1 if the @string is '', not when it's ' ' or '     ' or anything of the kind.

By the way, when you want the SUBSTRING function to return a substring from a certain position to the end of the string, you can specify any fairly large number as the length parameter, no need to use LEN(). Very often something like 

SUBSTRING(@string, 2, 999999999)

would do.

Related Question