Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » trailing space in len() problem
Add to briefcase
16 posts, Page 1 of 2
12»»

trailing space in len() problem Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
norbert.manyi
Posted Wednesday, September 19, 2012 6:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 17, 2013 3:17 AM
Points: 13, Visits: 73
hello all

shortly, this 
declare @sqlStr varchar(max)
set @sqlStr = 'aa, '
print len(@sqlStr)


returns 3 on one server and 4 on another...
if i try it with varchar(8000) i get 3 on both...

unfortunately using 8000 is not an option...

both servers have Latin1_General_CI_AS collation
Post #1361301
SQLmountain
Posted Wednesday, September 19, 2012 7:17 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, April 29, 2013 6:28 AM
Points: 436, Visits: 712
The LEN function won't give you trailing spaces. Try this instead:

declare @sqlStr varchar(max)
set @sqlStr = 'aa, '
print DATALENGTH(@sqlStr)
Post #1361315
norbert.manyi
Posted Wednesday, September 19, 2012 7:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 17, 2013 3:17 AM
Points: 13, Visits: 73
thanks, I will try this...

just a point... I'm not interested in trailing spaces, I'm only interested in consistency...

If I try the above-mentioned example with 'aa, a' I will get consistent values, which means that one of the servers ignore trailing spaces while using len(), one doesn't...
Post #1361350
SQLmountain
Posted Wednesday, September 19, 2012 7:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, April 29, 2013 6:28 AM
Points: 436, Visits: 712
It will be interesting to see what results you get. I should think they would be consistent.
Post #1361353
norbert.manyi
Posted Wednesday, September 19, 2012 8:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 17, 2013 3:17 AM
Points: 13, Visits: 73
Yes, they are indeed consistent...

Although I am still curious about why len() doesn't work...
Post #1361390
dwain.c
Posted Tuesday, September 25, 2012 10:12 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:03 AM
Points: 2,345, Visits: 3,191
ANSI_PADDING may not be set to the same value on both databases.

Try:

SELECT name, is_ansi_padding_on
FROM msdb.sys.databases
WHERE name = 'yourdatabase'


On both databases to check this.

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1364422
norbert.manyi
Posted Wednesday, September 26, 2012 1:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 17, 2013 3:17 AM
Points: 13, Visits: 73
gorgeous idea, but this isn't it

it's 0 on both servers...
Post #1364485
GilaMonster
Posted Wednesday, September 26, 2012 1:47 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 37,739, Visits: 30,013
What versions are those two servers?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1364488
norbert.manyi
Posted Wednesday, September 26, 2012 2:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 17, 2013 3:17 AM
Points: 13, Visits: 73
both are 2005

9.00.5000.00 on win 3790 on Intel x86
9.00.1399.06 on win 7601 on AMD x64
Post #1364503
GilaMonster
Posted Wednesday, September 26, 2012 3:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 37,739, Visits: 30,013
SQL 2005 RTM? Oooh.. There were some nasty issues around memory usage fixed in SP2.. I'd recommend you upgrade to SP4 on that one.

Also check your client connections, see if there's a difference in the ansi padding setting (it's a client option, what the DB has is just a default if not overridden)

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1364509
« Prev Topic | Next Topic »

Add to briefcase
16 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.