Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

DECLARE - 1 Expand / Collapse
Author
Message
Posted Tuesday, October 2, 2012 8:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, January 4, 2015 7:55 AM
Points: 5,333, Visits: 25,280
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

Before posting a performance problem please read
Post #1367377
Posted Tuesday, October 2, 2012 10:05 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:20 AM
Points: 1,372, Visits: 1,567
Thanks for this easy point Ron in the middle of the week

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1367390
Posted Tuesday, October 2, 2012 11:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:38 AM
Points: 14,461, Visits: 12,455
Oldie but a goodie



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1367404
Posted Tuesday, October 2, 2012 11:31 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:32 AM
Points: 2,046, Visits: 2,555
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/
Post #1367407
Posted Wednesday, October 3, 2012 12:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:06 AM
Points: 2,785, Visits: 4,792
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/
Post #1367416
Posted Wednesday, October 3, 2012 1:10 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 11:59 AM
Points: 4,497, Visits: 5,960
Easy one for middle of the week, thanks Ron

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1367423
Posted Wednesday, October 3, 2012 1:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:32 AM
Points: 2,046, Visits: 2,555
thanks Kingston..


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1367429
Posted Wednesday, October 3, 2012 2:12 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:36 AM
Points: 3,614, Visits: 3,909
Thanks Ron - easy one for me today

-------------------------------
Posting Data Etiquette - Jeff Moden
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
Post #1367456
Posted Wednesday, October 3, 2012 2:52 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:13 AM
Points: 1,987, Visits: 7,191
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!
Post #1367471
Posted Wednesday, October 3, 2012 2:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 8, 2015 12:46 PM
Points: 55, Visits: 98
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?
Post #1367472
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse