Quick question about len

Question

Quick question about len

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Jeff Moden (5/1/2010)
Absolutely my bad. Not sure why i was thinking that LEN would be affected by those. What I was thinking of was how much space was wasted in one particular database by using the wrong settings on a particular column and how LEN [font="Arial Black"]didn't [/font]find that problem.

Ah, true - that would be ugly. I've seen a few cases where I couldn't understand why they got so big only to find someone having monkeyed with the padding.

As to len - I was just trying to make sure i didn't miss something.

----------------------------------------------------------------------------------
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?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Matt Miller (#4) (5/1/2010)
As to len - I was just trying to make sure i didn't miss something.

Nope... you didn't miss a thing except for the short nap that I apparently took. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

But on the same server, different databases have same query with different results, it means that there should be some different database settings. if not then what else could be ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Bhuvnesh (5/2/2010)
But on the same server, different databases have same query with different results, it means that there should be some different database settings. if not then what else could be ?

If I ran into that behavior - I would start checking that those spaces really ARE spaces, and not some other non printing character. (BOL was specific about removing trailing spaces only).

Perhaps take a look at collation issues (sometimes the mapping or a collation clash might make characters be treated differently).

On the non-printing chars - take a look at this script: (space is ASCII 32)

;with ncte as (

select top 100 ROW_NUMBER() over (order by (select null)) rn

from sys.columns)

select rn,

'xxx'+char(rn)+'xxx',

len(CHAR(rn))

from ncte

Notice there are several at the beginning of the numbers that are non-printing, with a non-zero length.

----------------------------------------------------------------------------------
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?