DECLARE - 1

Question

Post reply

DECLARE - 1

Ron McCullough

SSC Guru

Points: 63877
More actions
October 2, 2012 at 8:14 pm

#268735

Comments posted to this topic are about the item DECLARE - 1
If everything seems to be going well, you have obviously overlooked something.
Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]
October 3, 2012 at 1:10 am

This was removed by the editor as SPAM

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

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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

Thanks for this easy point Ron in the middle of the week 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

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

Oldie but a goodie 🙂

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

can anyone explain me why select len(@a) as 'declared' gives output as 1

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

kapil190588 (10/2/2012)
can anyone explain me why select len(@a) as 'declared' gives output as 1

The answer to this is available in the explanation to the question( also shown below )

When n (Where n defines the string length) is not specified in a data definition or variable declaration statement, the default length is 1

Hence the two declaration shown below are equivalent

DECLARE @a VARCHAR ='xyz'

DECLARE @a VARCHAR(1) = 'xyz'

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

thanks Kingston..

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Stuart Davies SSCoach Points: 19505 More actions · Answer 6

Thanks Ron - easy one for me today

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Toreador SSChampion Points: 11365 More actions · Answer 7

As usual the lesson is not to rely on the defaults.

I'd have expected a near 100% success rate given the number of similar questions we've had in recent months, but obviously it still needs repeating!

Praveena-487125 SSC Journeyman Points: 79 More actions · Answer 8

Got to know that by default it takes the length as 1 if the size is not declared. Thank you....

However, I still did not get the difference between length of normal declaration and Cast/Convert... Please find below the queries....

1) SELECT

LEN('xyz') AS 'Declared'

, LEN(CONVERT(VARCHAR,'xyz')) AS 'Converted'

, LEN(CAST('xyz' AS VARCHAR)) AS 'Cast'

Ans : 3,3,3

2)

DECLARE @a VARCHAR ='xyz'

SELECT

LEN(@a) AS 'Declared'

, LEN(CONVERT(VARCHAR,@a)) AS 'Converted'

, LEN(CAST(@a AS VARCHAR)) AS 'Cast'

Ans : 1,1,1

I did not get default of 30 (which you mentioned in the answer in case of Cast/Convert) in what ever way I try... Can you please explain more on this?

DugyC Hall of Fame Points: 3804 More actions · Answer 9

Good back to basics question thanks, although I'm sure we've had previous on this topic.

Keep posting them though. 😀

_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

kapil_kk SSC-Insane Points: 21316 More actions · Answer 10

declare @a varchar(5)='56767'

select LEN(@a)

It will result into 56767.

DECLARE @a VARCHAR ='xyz'

SELECT

LEN(@a) AS 'Declared'

, LEN(CONVERT(VARCHAR,'xyz')) AS 'Converted'

, LEN(CAST('xyz' AS VARCHAR)) AS 'Cast'

When we use variable in Len method it will result us the length on basis of variable size. while in another statement LEN(CONVERT(VARCHAR,'xyz')) AS 'Converted' it is just convert the string 'xyz' into varchar, thus it result 3

select LEN(CONVERT(VARCHAR,'xyz')) AS 'Converted' -- Result 3

select LEN('xyx') -- Result 3

Same in case with CAST

Hope you get it now!! 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Lokesh Vij SSChampion Points: 10836 More actions · Answer 11

Praveena-487125 (10/3/2012)
I did not get default of 30 (which you mentioned in the answer in case of Cast/Convert) in what ever way I try... Can you please explain more on this?

execute the below query:

DECLARE @a VARCHAR(50) ='AAAAABBBBBCCCCCDDDDDEEEEEFFFFFGGGGG'

SELECT

LEN(@a) AS 'Declared'

, LEN(CONVERT(VARCHAR,@a)) AS 'Converted'

, LEN(CAST(@a AS VARCHAR)) AS 'Cast'

Result: 35, 30, 30

Hope it is clear now 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

kapil_kk SSC-Insane Points: 21316 More actions · Answer 12

How in case of

LEN(CONVERT(VARCHAR,@a)) AS 'Converted'

, LEN(CAST(@a AS VARCHAR)) AS 'Cast'

It is returning 30?

Can you plz exlpain.

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 13

Nice question for Wednesday. Thanks to everyone for the discussion.