April 13, 2013 at 2:29 am
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 an 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.
April 13, 2013 at 7:41 am
While I am not entirely certain I understand the use case, I do have some information that may help.
The SQL96 RFC declares trailing space as irrelevant. Therefore 'a<space>'='a'='a<space><space><space>'=....
You can use DataLen to detect the actual byte space consumed by the string.
So if you did something like this
Select DataLen('a<space><space>'), Len('a<space><space>')
You would see something like this
3 | 0
When you compare the difference between DataLen and Len and they are not the same, you can be sure that you have trailing spaces.
Hope that helps
Steve
April 15, 2013 at 2:03 am
Hello,
There is an alternative solution to your problem what you can do is, you can add an additional entries for extra spaces
for eg:
Let say your input is @STR = 'abc '
so now using
Len(@str) you get 3
but
using DATALENGTH(@str) you get 4
Now just simple subtract both i.e
while (DATALENGTH(@str) - LEN(@str)) > 0
BEGIN
Insert ' ' into @tbl
END
So now this you can get the appropriate number of spaces at the end.
Hope so this works for you .....:-)
April 15, 2013 at 5:15 pm
Pankaj067 (4/15/2013)
Hello,There is an alternative solution to your problem what you can do is, you can add an additional entries for extra spaces
for eg:
Let say your input is @STR = 'abc '
so now using
Len(@str) you get 3
but
using DATALENGTH(@str) you get 4
Now just simple subtract both i.e
while (DATALENGTH(@str) - LEN(@str)) > 0
BEGIN
Insert ' ' into @tbl
END
So now this you can get the appropriate number of spaces at the end.
Hope so this works for you .....:-)
There's no need for a loop here. Use REPLICATE or a calculated substring of existing spaces if you must have the trailing spaces.
Better yet, just cast it to CHAR and call it a day. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 15, 2013 at 5:30 pm
Folks, please consider avoiding all forms of RBAR for this simple problem. Also consider avoiding a MAX datatype unless you absolutely need it.
To avoid the RBAR, the following will work nicely and can easily be modified to handle a whole column instead of just one variable at a time.
DECLARE @STR NVARCHAR(MAX);
SELECT @STR = 'abcd ';
SELECT SUBSTRING(@Str,t.N,2000000000)
FROM dbo.Tally t
WHERE t.N > = 2
AND t.N <= DATALENGTH(@Str)/2
;
If you don't know what a Tally Table is or how it can be used to replace certain loops in a very high performance, single result set manner, then please see the following article. It will change your professional life for the better. It changed mine the very same day I learned about it.
http://www.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply