InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 20:01:05 GMT20http://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxNice question!Fri, 12 Oct 2012 09:02:03 GMT@Cassiehttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxBrilliant question ... and yeah, back to the basics!Wed, 10 Oct 2012 01:58:22 GMTKwexhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxNice one,back to basics simple n Useful :)Keep it upMon, 08 Oct 2012 23:00:08 GMTSamith Chttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxGood reminder, thanks !!Fri, 05 Oct 2012 09:57:58 GMTAndre Ranierihttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspx[quote][b]L' Eomot Inversé (10/4/2012)[/b][hr][quote][b]bitbucket-25253 (10/4/2012)[/b][hr]L' Eomot InverséNote in all of the following the bolding of parts of the explanation has been done by myself..........As for running my own tests, if I did do so, it was when I was using SQL 2000 and that would have been some 10 or 12 years ago, and to be truthful, I doubt if I did run tests back then. Naive enough back then to believe all I read in BOL as the truth and nothing but the truth.[/quote]Ron, The comments you are responding to you were in response to rmechaber's coment, and I certainly didn't intend to suggest that the correct answer and explanation was anything other that what you provided; the reference in your explanation has the same text about the length defaults in all 4 versions, and is the same page that I would consult to get this information. The SQL 2000 equivalent of that page doesn't specify a default for cast. Nor does the SQL 2005 page referenced by rmechaber, and I simply wanted to clarify whether his statement that the default length with CAST was different in SQL 2005 had any rational basis or was just an irrational (if without evidence, since it would require the SQL 2005 BoL page for CHAR and VARCHAR to be wrong) leap to a wrong conclusion from the absence of information at his reference. The char and varchar page in BoL for SQL 2000 also failed to specify a default for case (interestingly enough the nchar and nvarchar page specified 30 for cast but didn't specify anything for connect), but I'm fairly sure that it the defaults for length both with cast and with convert were both 30 (as stated on the SQL 2000 cast and convert page) although not 100% certain because can't test it.[/quote]Sorry, please accept my apologies for the mistake I made.Thu, 04 Oct 2012 14:28:52 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspx[quote][b]bitbucket-25253 (10/4/2012)[/b][hr]L' Eomot InverséNote in all of the following the bolding of parts of the explanation has been done by myself..........As for running my own tests, if I did do so, it was when I was using SQL 2000 and that would have been some 10 or 12 years ago, and to be truthful, I doubt if I did run tests back then. Naive enough back then to believe all I read in BOL as the truth and nothing but the truth.[/quote]Ron, The comments you are responding to you were in response to rmechaber's coment, and I certainly didn't intend to suggest that the correct answer and explanation was anything other that what you provided; the reference in your explanation has the same text about the length defaults in all 4 versions, and is the same page that I would consult to get this information. The SQL 2000 equivalent of that page doesn't specify a default for cast. Nor does the SQL 2005 page referenced by rmechaber, and I simply wanted to clarify whether his statement that the default length with CAST was different in SQL 2005 had any rational basis or was just an irrational (if without evidence, since it would require the SQL 2005 BoL page for CHAR and VARCHAR to be wrong) leap to a wrong conclusion from the absence of information at his reference. The char and varchar page in BoL for SQL 2000 also failed to specify a default for case (interestingly enough the nchar and nvarchar page specified 30 for cast but didn't specify anything for connect), but I'm fairly sure that it the defaults for length both with cast and with convert were both 30 (as stated on the SQL 2000 cast and convert page) although not 100% certain because can't test it.Thu, 04 Oct 2012 14:19:14 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxL' Eomot InverséNote in all of the following the bolding of parts of the explanation has been done by myself.When using BOL, at least the copy available to myself SQL Server 2005 [url]http://msdn.microsoft.com/en-us/library/ms176089(v=sql.90).aspx[/url][quote]When n is not specified in a data definition or variable declaration statement, the default length is 1.[b] When n is not specified when using the CAST and CONVERT functions, the default length is 30.[/b][/quote]SQL Server 2008 [url]http://msdn.microsoft.com/en-us/library/ms176089(v=sql.100).aspx[/url][quote]When n is not specified in a data definition or variable declaration statement, the default length is 1.[b] When n is not specified when using the CAST and CONVERT functions, the default length is 30.[/b][/quote]SQL Server 2012http://msdn.microsoft.com/en-us/library/ms176089(v=sql.110).aspx[quote]When n is not specified in a data definition or variable declaration statement, the default length is 1.[b] When n is not specified when using the CAST and CONVERT functions, the default length is 30.[/b][/quote]As for running my own tests, if I did do so, it was when I was using SQL 2000 and that would have been some 10 or 12 years ago, and to be truthful, I doubt if I did run tests back then. Naive enough back then to believe all I read in BOL as the truth and nothing but the truth.Thu, 04 Oct 2012 08:14:40 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxHi All,I am showing you this ambiguous behavior of length function in T-SQL.Concept:1)when data itself passes to 'LEN' function , then it returns no of characters. refer case-1.2)If u are passing variable inside a LEN function, then we will have three more cases.First of all, by default 'varchar' have default length is '1' in variable declaration time and '30' is in use inside 'CAST' and 'CONVERT' functions.refer case2,3,4.Case-1:DECLARE @a VARCHAR;SET @a='AAA' ;SELECT LEN('AAA') AS 'Declared', LEN(CONVERT(VARCHAR,'AAA')) AS 'Converted', LEN(CAST('AAA' AS VARCHAR)) AS 'Cast'RESULT:3 3 3CASE 2:DECLARE @a VARCHAR;SET @a='AAA' ;SELECT LEN(@a) AS 'Declared', LEN(CONVERT(VARCHAR,@a)) AS 'Converted', LEN(CAST(@a AS VARCHAR)) AS 'Cast'RESULT:1 1 1Exp: VARCHAR have '1' size and @a will contain only 'A'(one character).so, we look size according to size of datatype like in Declared section : @a have 1 character and VARCHAR also can hold only 1 character. So result is 1.Converted Section: @a have 1 character and VARCHAR can hold 30 character. So result is 1.(DATA<SIZE)Cast Section: same as Converted section.CASE 3:DECLARE @a VARCHAR(5);SET @a='AAABBBCCC' ;SELECT LEN(@a) AS 'Declared', LEN(CONVERT(VARCHAR,@a)) AS 'Converted', LEN(CAST(@a AS VARCHAR)) AS 'Cast'result:5 5 5Exp: same as case2 , now we have a VARCHAR(5).CASE 4:DECLARE @a VARCHAR(50);SET @a='Indias services sector makes up for over 60 percent of the countrys gross domestic product and a strong reading in HSBCs survey augurs well for the economy, where growth has faltered in recent months against the backdrop of political upheaval.Service sector activity grew at a faster clip in September led by firm demand, underscoring (its) resilience, said Leif Eskesen, an economist at HSBC.';SELECT LEN(@a) AS 'Declared', LEN(CONVERT(VARCHAR,@a)) AS 'Converted', LEN(CAST(@a AS VARCHAR)) AS 'Cast'RESULT:50 30 30Exp: Here, Declared:@a can contain 50 characters.so,it shows 50 as o/p.Converted: VARCHAR have default size 30, but data(@a) have 50 characters so it truncated to 30.Cast: same as converted.Thanks:Ajay Gaur9767860936Thu, 04 Oct 2012 04:32:36 GMTAjay gaurhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspx[quote][b]rmechaber (10/3/2012)[/b][hr]Please double-check the link before you contradict someone or call them "careless" or tell them what they "should be" doing: that page was from SQL Server 2005, which happens to be what I'm using.[/quote]I didn't see a link in your message - it was pretty well hidden. However, I did look up the 2005 BoL page that specifies the default length used for char and varchar with cast (the link is [url=msdn.microsoft.com/en-us/library/ms176089(v=sql.90).aspx][i]msdn.microsoft.com/en-us/library/ms176089(v=sql.90).aspx[/i][/url]) and it states that the default with CAST is 30. The SQL 2000 version of that page only gives the default for convert, and I guessed that that was what you were quoting. Now that I've looked at your message again I see there is a link in there , and that page does specify only the default for convert. So we have one BoL page for 2005 which says nothing about the default length with cast, and one which says it's 30. Should I take it that you believe that because one page dosn't specify the default you assume that the other page which does specify it is wrong, or have you actually run a test to check what the default value actually is?edit fix typosThu, 04 Oct 2012 03:59:06 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspx[quote][b]Vikas Pathak (10/4/2012)[/b][hr]I want to know how much space my variable occupied after assigning the values to that variable?[/quote]That's not how it works. When you assign the value 'xyz' to the variable, the variable itself is already a single character varchar, so only the first character of 'xyz' is actually assigned. The literal is 3 bytes long but the variable is just a single byte. It's [i]exactly[/i] the same as if you did this:[code="sql"]DECLARE @myVariable VARCHAR(1)SET @myVariable = 'xyz'[/code]Thu, 04 Oct 2012 03:57:32 GMTKevin Gillhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxThanks for reply.I know this its taking size 1 default.. But my doubt is .. I assign the values to that variable so it contains memory as per values. I want to know how much space my variable occupied after assigning the values to that variable?Thu, 04 Oct 2012 01:58:44 GMTVikas Pathakhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxneat and clear .. Thanks for the question .Thu, 04 Oct 2012 01:32:33 GMTdemonfoxhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxYou haven't specify the length of datatype in "DECLARE @myVariable AS varchar" that's why it is giving 1 as output.Thu, 04 Oct 2012 00:24:41 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxFollowing example will give idea that default size of the varchar and char in convert/cast has 30DECLARE @myVariable AS varchar(40);SET @myVariable = 'This string is longer than thirty characters';SELECT CAST(@myVariable AS varchar);SELECT DATALENGTH(CAST(@myVariable AS varchar)) AS 'VarcharDefaultLength';SELECT CONVERT(char, @myVariable);SELECT DATALENGTH(CONVERT(char, @myVariable)) AS 'VarcharDefaultLength';But still I have one doubt that why following expression giving 1 even I already assign values to variable.DECLARE @myVariable AS varcharset @myVariable = 'abc';DECLARE @myNextVariable AS charset @myNextVariable= 'abc';--The following returns 1SELECT DATALENGTH(@myVariable), DATALENGTH(@myNextVariable);GO/*(No column name) (No column name) 1 1*/Wed, 03 Oct 2012 23:35:30 GMTVikas Pathakhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxHere we can see the default size is 30 for the varchar and the char datatype.DECLARE @myVariable AS varchar(40);SET @myVariable = 'This string is longer than thirty characters';SELECT CAST(@myVariable AS varchar);SELECT DATALENGTH(CAST(@myVariable AS varchar)) AS 'VarcharDefaultLength';SELECT CONVERT(char, @myVariable);SELECT DATALENGTH(CONVERT(char, @myVariable)) AS 'VarcharDefaultLength';But still I have question that why following expression returning 1 even we already assign value into the variable.DECLARE @myVariable AS varcharset @myVariable = 'abc';DECLARE @myNextVariable AS charset @myNextVariable= 'abc';--The following returns 1SELECT DATALENGTH(@myVariable), DATALENGTH(@myNextVariable);GO--Output /*(No column name) (No column name) 1 1*/Wed, 03 Oct 2012 23:31:15 GMTVikas Pathakhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxThanks Ron.Wed, 03 Oct 2012 12:13:07 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspx[quote][b]bitbucket-25253 (10/3/2012)[/b][hr][quote][b]rmechaber (10/3/2012)[/b][hr][quote][b]L' Eomot Inversé (10/3/2012)[/b][hr][quote][b]rmechaber (10/3/2012)[/b][hr]Per [url=http://msdn.microsoft.com/en-us/library/ms187928%28v=sql.90%29.aspx]BOL[/url]:[quote] length Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types. For CONVERT, if length is not specified, the default to 30 characters. [i]sic[/i][/quote]Quoting a version of BoL that old is careless - tht is the wording from the BoL 2000 text; BoL versions for currently supported products SQL 2008, SQL 2008 R2, and SQL 2011 (and for SQL 2005, which I think has ended mainline support) all specify that the default length for when using CAST is 30. Remember that is a question doesn't specify teh SQL version it is supposed to work for all SQL versions still in full support, so you should not be looking at BoL for SQL 2000 to get the answer to this question.[quote]The CAST('xyz' AS VARCHAR) works the same as the initial DECLARE: if no length is specified for a VARCHAR, it defaults to 1.[/quote]The text doesn't state that - it just fails to state what the default is when CAST is used. I no longer have an SQL 2000 system so I can't test and see what it actually is, and I can't remember what it was either.[/quote]Please double-check the link before you contradict someone or call them "careless" or tell them what they "should be" doing: that page was from SQL Server 2005, which happens to be what I'm using.[/quote]Very strange when I click on the link I included in support of the corrrect answers I get the following:char and varchar (Transact-SQL)SQL Server 2012 Other Versions ... ... SQL Server 2008 R2 .. ... SQL Server 2008 ... ... SQL Server 2005[/quote]I think L'Eomot was referring to the hyperlink I provided to BOL in my reply (timestamp 2:27:11 pm), not in your original explanation.Wed, 03 Oct 2012 12:06:43 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspx[quote][b]rmechaber (10/3/2012)[/b][hr][quote][b]L' Eomot Inversé (10/3/2012)[/b][hr][quote][b]rmechaber (10/3/2012)[/b][hr]Per [url=http://msdn.microsoft.com/en-us/library/ms187928%28v=sql.90%29.aspx]BOL[/url]:[quote] length Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types. For CONVERT, if length is not specified, the default to 30 characters. [i]sic[/i][/quote]Quoting a version of BoL that old is careless - tht is the wording from the BoL 2000 text; BoL versions for currently supported products SQL 2008, SQL 2008 R2, and SQL 2011 (and for SQL 2005, which I think has ended mainline support) all specify that the default length for when using CAST is 30. Remember that is a question doesn't specify teh SQL version it is supposed to work for all SQL versions still in full support, so you should not be looking at BoL for SQL 2000 to get the answer to this question.[quote]The CAST('xyz' AS VARCHAR) works the same as the initial DECLARE: if no length is specified for a VARCHAR, it defaults to 1.[/quote]The text doesn't state that - it just fails to state what the default is when CAST is used. I no longer have an SQL 2000 system so I can't test and see what it actually is, and I can't remember what it was either.[/quote]Please double-check the link before you contradict someone or call them "careless" or tell them what they "should be" doing: that page was from SQL Server 2005, which happens to be what I'm using.[/quote]Very strange when I click on the link I included in support of the corrrect answers I get the following:char and varchar (Transact-SQL)SQL Server 2012 Other Versions ... ... SQL Server 2008 R2 .. ... SQL Server 2008 ... ... SQL Server 2005Wed, 03 Oct 2012 11:53:19 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspx[quote][b]L' Eomot Inversé (10/3/2012)[/b][hr][quote][b]rmechaber (10/3/2012)[/b][hr]Per [url=http://msdn.microsoft.com/en-us/library/ms187928%28v=sql.90%29.aspx]BOL[/url]:[quote] length Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types. For CONVERT, if length is not specified, the default to 30 characters. [i]sic[/i][/quote]Quoting a version of BoL that old is careless - tht is the wording from the BoL 2000 text; BoL versions for currently supported products SQL 2008, SQL 2008 R2, and SQL 2011 (and for SQL 2005, which I think has ended mainline support) all specify that the default length for when using CAST is 30. Remember that is a question doesn't specify teh SQL version it is supposed to work for all SQL versions still in full support, so you should not be looking at BoL for SQL 2000 to get the answer to this question.[quote]The CAST('xyz' AS VARCHAR) works the same as the initial DECLARE: if no length is specified for a VARCHAR, it defaults to 1.[/quote]The text doesn't state that - it just fails to state what the default is when CAST is used. I no longer have an SQL 2000 system so I can't test and see what it actually is, and I can't remember what it was either.[/quote]Please double-check the link before you contradict someone or call them "careless" or tell them what they "should be" doing: that page was from SQL Server 2005, which happens to be what I'm using.Wed, 03 Oct 2012 10:57:49 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspx[quote][b]rmechaber (10/3/2012)[/b][hr]Per [url=http://msdn.microsoft.com/en-us/library/ms187928%28v=sql.90%29.aspx]BOL[/url]:[quote] length Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types. For CONVERT, if length is not specified, the default to 30 characters. [i]sic[/i][/quote]Quoting a version of BoL that old is careless - tht is the wording from the BoL 2000 text; BoL versions for currently supported products SQL 2008, SQL 2008 R2, and SQL 2011 (and for SQL 2005, which I think has ended mainline support) all specify that the default length for when using CAST is 30. Remember that is a question doesn't specify teh SQL version it is supposed to work for all SQL versions still in full support, so you should not be looking at BoL for SQL 2000 to get the answer to this question.[quote]The CAST('xyz' AS VARCHAR) works the same as the initial DECLARE: if no length is specified for a VARCHAR, it defaults to 1.[/quote]The text doesn't state that - it just fails to state what the default is when CAST is used. I no longer have an SQL 2000 system so I can't test and see what it actually is, and I can't remember what it was either.edit: a couple of days on I think my first paragraph above was unfair - I failed to spot the link Rich provided, which is to the 2005 BoL page for cast and convert (which has the same omission as the 2000 BoL page for chard an varchar that I mistakenly though he was quoting) so he wasn't quoting quite as old a page as I thought (it is one for a release which is out of mainstream support, but nowhere near as old as an SQL 2000 one). My assumption that it was the 2000 page (just because it had that omission, when looking more carefully at the quoted text would have shown it wasn't the page I thought it was because that page doesn't refer to binary and varbinary) was careless, so I was committing the fault I accused Rich of.I'll stick by the second paragraph though: nothing in any of the pages concerned says that the default length for CAST is 1, and every release (from 2000 to 2011 inclusive) has at least one page that says that that default is 30.Wed, 03 Oct 2012 10:54:50 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspx[quote][b]rmechaber (10/3/2012)[/b][hr][quote][b]Kevin Gill (10/3/2012)[/b][hr][quote][b]rmechaber (10/3/2012)[/b][hr]The CAST('xyz' AS VARCHAR) works the same as the initial DECLARE: if no length is specified for a VARCHAR, it defaults to 1.Rich[/quote]But that suggests that running [code="sql"]SELECT LEN(CAST('xyz' as VARCHAR))[/code] should return 1, not 3 as it does.Also [code="sql"]SELECT LEN(CAST('abcdefghijklmnopqrttuvwxyz1234567890' as VARCHAR))[/code] returns 30, suggesting that CAST does behave as per CONVERT.[/quote]Interesting. I confess I didn't look into this much more than to check BOL for CONVERT.I don't really find much useful about questions like this: follow recommended practice and always specify a data length, and this issue disappears. But maybe that's the lesson of the day.Rich[/quote]Yes it is the lesson of the day, and from the results so far, it appears that many might/hopefully have learned something[center]Correct answers: 68% (456)Incorrect answers: 32% (219)Total attempts: 675[/center]Wed, 03 Oct 2012 10:04:44 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxShows both a trick and a trap. Might not use either but might be good to remember when trying to determine why something does not work exactly as expected. Excellent question!Wed, 03 Oct 2012 09:43:52 GMTMiles Nealehttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxThis is a good example of why you should always specify length for your datatypes when applicable.Wed, 03 Oct 2012 09:23:15 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspx[quote][b]rmechaber (10/3/2012)[/b][hr]follow recommended practice and always specify a data length, and this issue disappears. But maybe that's the lesson of the day.[/quote]I completely agree. Code that assumes default behaviour is always subject to break things later in interesting ways when the defaults change...Wed, 03 Oct 2012 08:52:08 GMTKevin Gillhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspx[quote][b]Kevin Gill (10/3/2012)[/b][hr][quote][b]rmechaber (10/3/2012)[/b][hr]The CAST('xyz' AS VARCHAR) works the same as the initial DECLARE: if no length is specified for a VARCHAR, it defaults to 1.Rich[/quote]But that suggests that running [code="sql"]SELECT LEN(CAST('xyz' as VARCHAR))[/code] should return 1, not 3 as it does.Also [code="sql"]SELECT LEN(CAST('abcdefghijklmnopqrttuvwxyz1234567890' as VARCHAR))[/code] returns 30, suggesting that CAST does behave as per CONVERT.[/quote]Interesting. I confess I didn't look into this much more than to check BOL for CONVERT.I don't really find much useful about questions like this: follow recommended practice and always specify a data length, and this issue disappears. But maybe that's the lesson of the day.RichWed, 03 Oct 2012 08:33:43 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspx[quote][b]Toreador (10/3/2012)[/b][hr]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![/quote]I think in this case the number wrong indicates that few people use varchar without specifying a length so this default stuff is a weird special case. I knew the declaration was length 1. I think it serve one right to get a nearly useless default. I don't understand the inconsistency with the convert/cast behavior being so "forgiving."Wed, 03 Oct 2012 08:27:29 GMTMike Dougherty-384281http://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspx[quote][b]rmechaber (10/3/2012)[/b][hr]The CAST('xyz' AS VARCHAR) works the same as the initial DECLARE: if no length is specified for a VARCHAR, it defaults to 1.Rich[/quote]But that suggests that running [code="sql"]SELECT LEN(CAST('xyz' as VARCHAR))[/code] should return 1, not 3 as it does.Also [code="sql"]SELECT LEN(CAST('abcdefghijklmnopqrttuvwxyz1234567890' as VARCHAR))[/code] returns 30, suggesting that CAST does behave as per CONVERT.Wed, 03 Oct 2012 08:07:30 GMTKevin Gillhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxNice question, thanks!Wed, 03 Oct 2012 07:35:46 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspx[quote][b]Dave62 (10/3/2012)[/b][hr][quote][b]kapil190588 (10/3/2012)[/b][hr]how Casting or converting a string literal behaves returning 30 characters. this is the only thing on which am stuck..plz explain[/quote]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.[/quote]Nope, ANSI_PADDING is a red herring here: you get the same results with it set to OFF.Per [url=http://msdn.microsoft.com/en-us/library/ms187928%28v=sql.90%29.aspx]BOL[/url]:[quote] length Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types. For CONVERT, if length is not specified, the default to 30 characters. [i]sic[/i][/quote]The CAST('xyz' AS VARCHAR) works the same as the initial DECLARE: if no length is specified for a VARCHAR, it defaults to 1.RichWed, 03 Oct 2012 07:27:11 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspx[quote][b]kapil190588 (10/3/2012)[/b][hr]how Casting or converting a string literal behaves returning 30 characters. this is the only thing on which am stuck..plz explain[/quote]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.Wed, 03 Oct 2012 06:57:48 GMTDave62http://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxOne 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.Wed, 03 Oct 2012 06:43:24 GMTjeff.masonhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxhow Casting or converting a string literal behaves returning 30 characters. this is the only thing on which am stuck..plz explainWed, 03 Oct 2012 06:17:25 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspx[quote][b]Praveena-487125 (10/3/2012)[/b][hr]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,32) 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,1I 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?[/quote]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:[code="sql"]DECLARE @a VARCHAR = 'xyz' [/code]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 [code="sql"]CAST('xyz' as VARCHAR)[/code] or [code="sql"]CONVERT(VARCHAR,'xyz')[/code] you get a 30 character varchar with only three characters in it, so the 'LEN' call returns 3.In your first version you call [code="sql"]LEN('xyz')[/code] 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...KevWed, 03 Oct 2012 06:10:17 GMTKevin Gillhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspx[quote][b]kapil190588 (10/3/2012)[/b][hr]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.[/quote]Please check my previous post, Praveena has the same question..Wed, 03 Oct 2012 04:48:44 GMTLokesh Vijhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxNice question for Wednesday. Thanks to everyone for the discussion.Wed, 03 Oct 2012 04:41:56 GMT(Bob Brown) http://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxHow in case of LEN(CONVERT(VARCHAR,@a)) AS 'Converted', LEN(CAST(@a AS VARCHAR)) AS 'Cast'It is returning 30?Can you plz exlpain.Wed, 03 Oct 2012 04:04:09 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspx[quote][b]Praveena-487125 (10/3/2012)[/b][hr]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?[/quote]execute the below query:[code="sql"]DECLARE @a VARCHAR(50) ='AAAAABBBBBCCCCCDDDDDEEEEEFFFFFGGGGG' SELECT LEN(@a) AS 'Declared', LEN(CONVERT(VARCHAR,@a)) AS 'Converted', LEN(CAST(@a AS VARCHAR)) AS 'Cast'[/code]Result: 35, 30, 30 Hope it is clear now :-)Wed, 03 Oct 2012 03:55:35 GMTLokesh Vijhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxdeclare @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 3select LEN(CONVERT(VARCHAR,'xyz')) AS 'Converted' -- Result 3select LEN('xyx') -- Result 3Same in case with CASTHope you get it now!! :)Wed, 03 Oct 2012 03:36:59 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxGood back to basics question thanks, although I'm sure we've had previous on this topic.Keep posting them though. :-DWed, 03 Oct 2012 03:19:04 GMTDugyChttp://www.sqlservercentral.com/Forums/Topic1367377-1222-1.aspxGot 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,32) 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,1I 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?Wed, 03 Oct 2012 02:53:20 GMTPraveena-487125