VARCHAR datatype

  • Comments posted to this topic are about the item VARCHAR datatype

  • Could call it a trick... in another form:

    select * from(

    select 'red'

    union select 'red'

    union select 'blue'

    union select 'green'

    union select 'yellow'

    union select 'orange'

    union select 'purple')x

    where textfield like 'red' -- or like '%red%' same thing

    In the above form, it's obvious we have no field named "textfield".

    Jamie

  • Nice question.

    When the length is not specified in a data definition or variable declaration statement, the default length is 1 ----- Didn't knew this. used to give length to all variables.

    SQL DBA.

  • Nice question. I got it right cause I made an effort to examine all kinds of default parameters after encountering something similar. Not 100% sure but I think the rule of default length applies only to variable declaration; functions have other rules: CONVERT(VARCHAR, expression) defaults to VARCHAR(30).

    My 2c

    Regards,

    Hrvoje

    Hrvoje Piasevoli

  • Jamie Longstreet-481950 (7/17/2010)


    Could call it a trick... in another form:

    select * from(

    select 'red'

    union select 'red'

    union select 'blue'

    union select 'green'

    union select 'yellow'

    union select 'orange'

    union select 'purple')x

    where textfield like 'red' -- or like '%red%' same thing

    In the above form, it's obvious we have no field named "textfield".

    Don't see how this relates to the posted question. No trick in the question just default behavior for unspecified optional parameter.

    Hrvoje Piasevoli

  • Textfield is the field name... are we not saying the same thing? The textfield is not the same for both the variable and the temp test table. If you remove the temp table and use a sub-query, it makes the issue less confusing.

    You'll excuse me... I'm griping. I don't like the way the question was worded. Should have said that in the first place.

    Jamie

  • Both 1 and 4 could be the correct answer as Green, Orange and Purple do not actually contain a "R", so if you have a case sensitive collation you would only have one row returned. But I figured that wasn't you were looking for, and "It depends" wasn't a choice. 😉

    In any case, nice question, thanks!

  • Jamie Longstreet-481950 (7/18/2010)


    Textfield is the field name... are we not saying the same thing? The textfield is not the same for both the variable and the temp test table. If you remove the temp table and use a sub-query, it makes the issue less confusing.

    I don't understand what you are trying to say. Yes Textfield is the name, but it has nothing to do with the variable, and your example isn't at all what the question is about.

    The issue is the "DECLARE @Testvar VARCHAR" and knowing that that means the same as "DECLARE @Testvar VARCHAR(1)" and that the SET truncates the data and does not report an error. I'm not sure why you think that is confusing.

  • This could make the Q "less confusing" but less real-life and interesting:

    DECLARE @Testvar VARCHAR;

    SET @Testvar = 'Red';

    print @Testvar;

    If only there was a remark of the collation set (as UMG developer pointed out) to avoid ambiguity.

    Hrvoje Piasevoli

  • I agree, the collation setting (whether it's case sensitive or not) would bring different results (1 for case-sensitive and 4 for case-insensitive).


    Urbis, an urban transformation company

  • Nice question!!

  • Nice question.

    I did not notice the variable length.that's why i got wrong.I had answered 1.

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • Hmmmm, it appears I don't have much of a point, but, if I did, it would be that it is not good practice to cast a variable unless it is strongly typed.

    select * from(

    select 'red' textfield

    union select 'blue' textfield

    union select 'green' textfield

    union select 'yellow' textfield

    union select 'orange' textfield

    union select 'purple' textfield)x

    where convert(varchar(1),textfield) like '%red%'

    ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/282cd982-f4fb-4b22-b2df-9e8478f13f6a.htm

    When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.

    Not 100% sure but I think the rule of default length applies only to variable declaration; functions have other rules: CONVERT(VARCHAR, expression) defaults to VARCHAR(30).

    But, I was unaware that the varchar declaration has a default. Normally, I wouldn't think of trying this so now the question has forced me to think outside the box. To wit, perhaps it is a good question after all.

    Jamie

  • A good question about a common misunderstanding. The number of times when a problem posted to usenet or forums could be solved by simply adding the length to a varchar parameter is staggering.

    My only minor gripe is, as already noted by others, the lack of collate indication. On my default test server, this query would have returned 1 row, as I use a case sensitive collation there. Fortunately I assumed out of the box default settings for the instance, so I replied 4 and got it right.

    Thanks for the great question; I'm looking forward to seeing more of these, Greg!


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • SanjayAttray (7/18/2010)


    Nice question.

    When the length is not specified in a data definition or variable declaration statement, the default length is 1 ----- Didn't knew this. used to give length to all variables.

    And I really hope that you continue to give length to all variables. Relying on this default length is bad practice. And very confusing, since the default length depends on where the data type is used - in a CONVERT or CAST function, the default is 30; elsewhere it is 1.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

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

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