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 Wednesday, October 3, 2012 3:19 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 4:28 AM
Points: 1,248, Visits: 779
Good back to basics question thanks, although I'm sure we've had previous on this topic.

Keep posting them though.


_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
Post #1367483
Posted Wednesday, October 3, 2012 3:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,893, Visits: 2,329
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/
Post #1367493
Posted Wednesday, October 3, 2012 3:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
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
Guidelines for answers on Performance questions

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

Follow me @Twitter

Post #1367510
Posted Wednesday, October 3, 2012 4:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,893, Visits: 2,329
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/
Post #1367520
Posted Wednesday, October 3, 2012 4:41 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 12, 2014 4:19 AM
Points: 701, Visits: 1,145
Nice question for Wednesday. Thanks to everyone for the discussion.
Post #1367537
Posted Wednesday, October 3, 2012 4:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
kapil190588 (10/3/2012)
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.


Please check my previous post, Praveena has the same question..


~ 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 #1367546
Posted Wednesday, October 3, 2012 6:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:53 AM
Points: 1,105, Visits: 301
Praveena-487125 (10/3/2012)
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?

You're changing the question by your examples here - it's about what the resultant variable type ends up as in each case - putting @a into each one fundamentally changes what's being asked, as does not using @a at all. It's only when you mix them as in the original question that you will get the specified behaviour.

If you explicitly declare a variable as just 'VARCHAR' it has a default length of 1 character. As such:

DECLARE @a VARCHAR = 'xyz' 

actually creates @a as a varchar(1) which contains only the character 'x'.

Casting or converting a string literal behaves differently and defaults to 30 characters so when you
CAST('xyz' as VARCHAR)

or
CONVERT(VARCHAR,'xyz')

you get a 30 character varchar with only three characters in it, so the 'LEN' call returns 3.

In your first version you call
LEN('xyz')

which is taking the length of a string literal, which is implicitly cast to a VARCHAR type so internally will be represented (someone correct me if I'm wrong here) as a VARCHAR(30) and hence the length shows as 3. It's only when you declare it as a VARCHAR explicitly that you get length of 1. That's what happens with your second version where you use the explicitly declare length 1 varchar for all your length checks.

Hope that's clearer than it feels...

Kev




-------------------------------
Oh no!
Post #1367600
Posted Wednesday, October 3, 2012 6:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,893, Visits: 2,329
how Casting or converting a string literal behaves returning 30 characters.
this is the only thing on which am stuck..
plz explain



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1367608
Posted Wednesday, October 3, 2012 6:43 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:02 AM
Points: 989, Visits: 1,825
One comment -- the syntax of the question only works on SQL 2008 and up. This line:

DECLARE @a VARCHAR ='xyz'

is not supported on earlier versions; you have to declare and select as two steps.
Post #1367633
Posted Wednesday, October 3, 2012 6:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 2,071, Visits: 2,064
kapil190588 (10/3/2012)
how Casting or converting a string literal behaves returning 30 characters.
this is the only thing on which am stuck..
plz explain


I think it has to do with "SET ANSI_PADDING ON" and the default length of varchar being 30.

Just answer off the top of my head from memory. If anyone knows better feel free to correct.
Post #1367644
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse