August 21, 2008 at 2:41 pm
I am having a problem with strings.
DECLARE @string NVARCHAR(10)
SET @String = 'test, '
SELECT SUBSTRING(@String, 1, LEN(@STRING)-2)
If I run the code above on one of my servers I get the result 'test'. Basically, I got rid of the space and the comma.
However, when I run the same code on a different server I get the result 'tes'. It is not counting the space.
Why is this?
August 21, 2008 at 3:00 pm
Check your ansi_padding settings.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 21, 2008 at 3:09 pm
Both databases have 'ANSI Padding Enabled' set to false.
August 21, 2008 at 3:58 pm
I'm interested to see the cause. I would have said the same thing about ANSI Padding. You could use the DATALENGTH() function instead which should get you what you want. Since its NVARCHAR() i think you would do something like substring(.... (DATALENGTH(field)/2) -2). Not the solution, but a possible fix until figure out why you have that issue.
August 21, 2008 at 4:12 pm
Are you SURE that is in fact a blank space on the initial server, and not some non-printing character?
According to Books Online, the len() function....
Returns the number of characters of the specified string expression, excluding trailing blanks.
In which case neither should have given you "test" if that's a space. If on the other hand - that's some other kind of character (like, a TAB), or something non-printing, that might get counted.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 21, 2008 at 4:19 pm
I'm with Matt on this one. As far as I know, it should always ignore the trailing spaces. DATALENGTH is the solution that you want for this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 21, 2008 at 5:51 pm
rbarryyoung (8/21/2008)
I'm with Matt on this one. As far as I know, it should always ignore the trailing spaces. DATALENGTH is the solution that you want for this.
Here's some more background on the problem.
I am constructing a dynamic sql string to be executed.
(I needed to do that because we have a table storing the column names to be selected.)
I concatenate my list of columns with a comma and then a space.
On my testing environment, and even on our production server SQL Server was counting the last space as a character and stripping out trailing space and comma. However, when the same code was deployed to the client's server, the code bombed because an extra character was taken out.
We have temporarily resolved the issue by only concatenating a comma and not a space, and stripping out the last comma at the end.
I do need to know why our server is counting the space and the other server is not, in order to prevent more errors like this.
Any help is appreciated. 🙂
August 22, 2008 at 2:41 am
ggraber (8/21/2008)
We have temporarily resolved the issue by only concatenating a comma and not a space, and stripping out the last comma at the end.
It is possible to make sure that the delimiter is not added to the end of concatenated string by using ISNULL (or COALESCE) function... so that you don't have to remove it afterwards. I'm not sure whose post it was, but I remember that I learned it on these forums some time ago.
CREATE TABLE Colors(id INT, Color VARCHAR(20))
INSERT INTO Colors VALUES (1, 'Red')
INSERT INTO Colors VALUES (2, 'Green')
INSERT INTO Colors VALUES (3, 'Blue')
DECLARE @result VARCHAR(100)
SELECT @result=ISNULL(@result+ ', ','') + c.Color
FROM colors c
SELECT @result
DROP TABLE colors
I do need to know why our server is counting the space and the other server is not, in order to prevent more errors like this.
Any help is appreciated. 🙂
As far as I know, and as several people have remarked already, LEN never counts trailing spaces... so question is not why the other server is not counting - it is why your server IS counting the space. Check it again please - copy the code you posted and run it on your server. It should return 3 letters only = "tes". If that does not not work, even after you have verified that the character at the end really is space, then I have no idea what's happening here.
August 22, 2008 at 6:19 am
The way to check for a space at the end is:
Select Ascii(Right(@string, 1))
A space will return 32. Any other value is not a space (TAB returns a 9).
For instance, cut and paste the following code, verbatim, into a query window:
select
ascii(' '),
ascii('')
See how the spaces in the two "ascii" function look identical (even though they may look different here, in the forum display). Now execute it, and you can see that the second one is really a TAB.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 22, 2008 at 7:10 am
Just to reiterate the original posters comments, he is aware of how to get past the issue but he does need to know what it is happening on the one server.
At this point I don't have the answer BUT, I will try to look at it a bit today to see what I can come up with. Pretty odd.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 22, 2008 at 7:54 am
DavidB (8/22/2008)
Just to reiterate the original posters comments, he is aware of how to get past the issue but he does need to know what it is happening on the one server.At this point I don't have the answer BUT, I will try to look at it a bit today to see what I can come up with. Pretty odd.
Yes. I'm looking for why the issue is happening not how to get around it.
I've found some more interesting information.
This seems to happen when I use NVARHCAR(MAX) and not when I give an actual size to the character string.
Take a look at this code:
-- ******* USING NVARCHAR(MAX) ******* --
DECLARE @test-2 NVARCHAR(MAX)
SET @test-2 = 'TEST, '
Select Ascii(Right(@TEST, 1))
-- RETURNS 32
SELECT LEN(@TEST)
-- RETURNS 6
SELECT substring(@TEST,1,len(@TEST)-1)
-- RETURNS 'TEST,'
SELECT substring(@TEST,1,len(@TEST)-2)
-- RETURNS 'TEST'
-- ******* USING NVARCHAR(10) ******* --
DECLARE @TEST2 NVARCHAR(10)
SET @TEST2 = 'TEST, '
Select Ascii(Right(@TEST2, 1))
-- RETURNS 32
SELECT LEN(@TEST2)
-- RETURNS 5
SELECT substring(@TEST,1,len(@TEST2)-1)
-- RETURNS 'TEST'
SELECT substring(@TEST,1,len(@TEST2)-2)
-- RETURNS 'TES'
However, this doesn't explain why it is working differently on different environments.
August 22, 2008 at 8:13 am
OK, now that's weird.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 22, 2008 at 8:21 am
ggraber (8/21/2008)
Both databases have 'ANSI Padding Enabled' set to false.
The ANSI_PADDING SETTING is set at the connection level and by default SSMS and .NET connections set ANSI_PADDINGS to TRUE, so any columns/variables created in SSMS without explicitly turning ANSI_PADDING OFF are created with ANSI_PADDING ON and are supposed to behave accordingly, based on some testing I have done this isn't consistent. The option to change that is being deprecated. From BOL:
Important:
In a future version of 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.
...
Note:
We recommend that ANSI_PADDING always be set to ON.
Matt & Barry are correct in stating that special characters (Tab, CR, LF) are treated differently than "Trailing blanks"(spaces). I know this doesn't get to the point of why it is behaving differently on different servers, but I do have a couple of questions:
Are the servers running on the same build? Are the servers running on the same OS and OS build?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 22, 2008 at 8:30 am
Also from MSDN under NVARCHAR at: http://msdn.microsoft.com/en-us/library/ms186939(SQL.90).aspx
SET ANSI_PADDING is always ON for nchar and nvarchar. SET ANSI_PADDING OFF does not apply to the nchar or nvarchar data types.
--SJT--
By the way, has anyone been able to duplicate ggraber's results?
August 22, 2008 at 8:33 am
Jack Corbett (8/22/2008)t to false.
Are the servers running on the same build? Are the servers running on the same OS and OS build?
Both servers are running Windows Server 2003 SP2.
As for build: our server is running 9.0.1399 and clients server is running 9.0.3068
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply