Counting spaces with datalength

Question

Post reply

Counting spaces with datalength

Oleg Netchaev

SSCertifiable

Points: 5276
More actions
March 1, 2010 at 11:11 pm

#211752

Comments posted to this topic are about the item Counting spaces with datalength

Viewing 15 posts - 1 through 15 (of 18 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Interesting Question

Thanks

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Kelsey Thornton SSCrazy Points: 2157 More actions · Answer 2

Looked at the MSDN article posted before trying to answer - had to search elsewhere as the MSDN en try wasn't very illuminating 🙂

Kelsey Thornton
MBCS CITP

ziangij SSCrazy Eights Points: 8066 More actions · Answer 3

thanks for the question.... was really not aware of datalength function..

ronmoses@gmail.com SSCarpal Tunnel Points: 4480 More actions · Answer 4

When your name is in the explanation, and you get it wrong, well that's just embarrassing. :unsure:

Ron Moses

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

tmacs33 SSC Eights! Points: 975 More actions · Answer 5

Very interesting. I typically use LEN but I can see how datalength would be useful.

sanbornd SSC-Addicted Points: 412 More actions · Answer 6

Great question; it also really highlights the difference between Len() and DataLength(). For fun folks should try substituting Len for DataLength.

Tom Garth SSCertifiable Points: 6193 More actions · Answer 7

Nice question. Makes ya think.

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

TomThomson SSC Guru Points: 104773 More actions · Answer 8

Good question.

I don't know whether to be more worried that 198 people so far thought datalength behaved like length or that 118 thought 11 was an even number!

Tom

benkoskysa SSC Enthusiast Points: 149 More actions · Answer 9

-- Sorry, I get 1 for count(1) using datalength(), record 8.

-- count(1) for Len() rerturns 0

-- What am i doing wrong?

oops, nevermind...

benkoskysa SSC Enthusiast Points: 149 More actions · Answer 10

With the given SQL, @temp is an empty table, therefore count(1) will always return 0.

Peter Trast SSCarpal Tunnel Points: 4392 More actions · Answer 11

benkoskysa (3/3/2010)
With the given SQL, @temp is an empty table, therefore count(1) will always return 0.

Not if you replace DATALENGTH with LEN in this query 😉 I have to try everything myself, take NOBODY's word for anything... yes, I am a trusting soul...

--Mileage may vary--

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems

benkoskysa SSC Enthusiast Points: 149 More actions · Answer 12

Good catch, thank you.

It turns out that @table was empty because I used a database where the sys.master_files table was empty; therefore, no rows for the cte...

Live and learn

🙂

Fal Hall of Fame Points: 3143 More actions · Answer 13

benkoskysa (3/3/2010)
... I used a database where the sys.master_files table was empty...

Well, it can't be empty. At the very least you can expect 8 rows from a brand-spankin' new install, 2 rows for each of master, tempdb, msdb and model. It's likely you don't have the right permissions to this view.

Although, this suggests 2 things: 1) that there is now another reason that people who get a QotD wrong can claim it is invalid - coz the required permissions weren't specified; and 2) that many cut-n-pasters will get the correct answer for the wrong reason.

S.

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 14

Fal (3/3/2010)
benkoskysa (3/3/2010)
... I used a database where the sys.master_files table was empty...
Well, it can't be empty. At the very least you can expect 8 rows from a brand-spankin' new install, 2 rows for each of master, tempdb, msdb and model. It's likely you don't have the right permissions to this view.
Although, this suggests 2 things: 1) that there is now another reason that people who get a QotD wrong can claim it is invalid - coz the required permissions weren't specified; and 2) that many cut-n-pasters will get the correct answer for the wrong reason.
S.

I just should have opted to use a different view to populate the table with 10 records. I chose the sys.master_files for 3 reasons:

1. It has 2 rows for each of the visible system databases (no rows are visible for resource), making it 8 in total

2. Usually we have at least one non-system database (if we have a job :-)), which adds 2 more records, making it at least 10 in total, which satisfies the test requirement to have 10 records needed for QoD

3. The total number of records is still pretty small so the cte part is not heavy.

I guess the better choice would be to opt for something not heavy but less restrictive, for example

select

top 10

row_number() over (order by table_name) record_number

from information_schema.tables

for the cte part. Thank you for pointing out the flaws in my question. I will try to be more accurate next time.

Oleg