Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
why are you using the table variable @tbl? You've got 1 row with 1 column. Just use @str. Also, what is the purpose of @i? you don't use it anywhere. –  StrayCatDBA Apr 13 '13 at 8:38
    
This might help some as well: SELECT LEN(N'a '), LEN(CAST(N'a ' AS VARBINARY(MAX)))/2 –  StrayCatDBA Apr 13 '13 at 8:44
    
@StrayCatDBA In my question i have shown just an example of the problem causing me. Actually table has so many rows etc etc. So i did a small code which can explain problem causing me and you can also easily understand –  IT researcher Apr 13 '13 at 9:10
add comment

4 Answers

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.

share|improve this answer
add comment
up vote 3 down vote accepted

Instead of using LEN use DATALENGTH. But care should be taken while handing nvarchar datatype as datalength returns bytes used so it will be double of characters used. In nvarchar a character takes two bytes.

So for the query given in question use the below.

declare @str nvarchar(max),@i int,@len int
set @i=0
set @str='abc '
set @len=DATALENGTH(@str)/2
declare @tbl Table(a  nvarchar(max)) 
insert @tbl select @str
while (select @i) < @len
begin 
    set @i=@i+1
    set @str = substring(@str,2,len(@str))
    update  @tbl set a=@str
            select * from @tbl
end
share|improve this answer
add comment

Not entirely sure what you are asking for.

However, if it relates to the space at the end of the string are you being affected by the value of the ANSI_PADDING setting at the time of table creation? See: http://msdn.microsoft.com/en-us/library/ms187403.aspx

Depending on the setting, the space(s) will either be left there or trimmed at insert into the table. That article even includes an example for you that demonstrates the scenario.

share|improve this answer
    
But have you seen that link you have mentioned(about ANSI_PADDING) properly? It states "In a future version of Microsoft SQL Server ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature." So i think it should not be used. –  IT researcher Apr 13 '13 at 12:08
    
I have seen that and am well aware of it. But I have no idea if the original poster is. However; I should have also added they should use LTRIM if they want to strip off the trailing spaces in their original insert. –  Kent Chenery Apr 14 '13 at 8:18
add comment

I honestly don't understand the question at all, but as was mentioned in the comment above, you can just LTRIM / RTRIM to get rid of the spaces in either end of the string. Or alternatively use REPLACE(@Str, ' ', '') to replace all whitespace characters within the string. Also, some encoding / linebreak problems etc may have made it to the field.

Sometimes I've seen cases with CHAR(10) without CHAR(13) (carriage return vs. linefeed) for instance, which may look like a simple whitespace, but isn't the same. That's one possible avenue to check up on too. You can fix those in a similar manner by using REPLACE(@str, CHAR(10), ''), etc.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.