What's the Length?

Question

What's the Length?

Umer Akram

SSC-Insane

Points: 22209
More actions
January 9, 2016 at 11:33 am

#323463

Comments posted to this topic are about the item What's the Length?
January 10, 2016 at 10:23 pm

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

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

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 1

Interesting question but an easy one for me only because I remembered that the default value of the max length of a string is 4000. I have to admit that I needed 5 minutes to be sure of the correct value ( 3996 ) in this case.

The solution you provided is interesting and useful at least according to me. I am sure I would need 15 minutes to find it as I have not used this function since the release of SQL Server 2008 a long time ago ... ( pure T-SQL is not my preferred topic , but I will do some tests in the next days ).

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Great question to start the week. Thanks!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

The MAX lengths will get you every time when it comes to lengths and joining to them. Interesting question.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

I would actually change the variable definition.

DECLARE

@max-2 AS NVARCHAR(MAX) = ''

, @Var AS NVARCHAR(MAX) = 'ABCabc123';

SELECT

@max-2 = REPLICATE(@Var, 445);

SELECT

CASE WHEN LEN(@Max) = LEN(@Var) * 445 THEN 'Simple math'

ELSE 'It''s complicated'

END;

This is a common problem when creating long dynamic sql strings. I hope that people learn from this question.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

david.gugg SSCertifiable Points: 6096 More actions · Answer 5

Fun questions, thanks Umer Akram.

Be still, and know that I am God - Psalm 46:10

Rich Mechaber SSChampion Points: 11130 More actions · Answer 6

Interesting question. I learned something, b/c I don't use REPLICATE() for long strings.

The explanation from BOL is quite specific:

If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.

Rich

George Vobr SSCrazy Eights Points: 9996 More actions · Answer 7

A really interesting question , thanks Umer . But just simple complicated? 😉

DECLARE

@max-2 AS NVARCHAR(MAX) = ''

, @Var AS VARCHAR(4000) = 'ABCabc123';-- Varchar instead Nvarchar --

SET @max-2 = REPLICATE(@Var, 445);

SELECT LEN(@Max) as LenMax, LEN(@Var) * 445 as LenVar,

DATALENGTH(@Max) as DataLenMax, DATALENGTH(@Var) * 445 as DataLenVar;

Ref.: https://msdn.microsoft.com/en-us/library/ms174383.aspx

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 8

akljfhnlaflkj

SSC Guru

Points: 76202

February 17, 2016 at 4:43 pm

#1859424

Nice question, thanks.