July 17, 2010 at 12:17 pm
Comments posted to this topic are about the item VARCHAR datatype
July 17, 2010 at 4:38 pm
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
July 18, 2010 at 7:25 am
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.
July 18, 2010 at 12:51 pm
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
July 18, 2010 at 12:56 pm
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
July 18, 2010 at 1:22 pm
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
July 18, 2010 at 2:40 pm
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!
July 18, 2010 at 2:45 pm
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.
July 18, 2010 at 3:11 pm
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
July 19, 2010 at 12:08 am
Nice question!!
July 19, 2010 at 12:31 am
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)
July 19, 2010 at 1:06 am
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
July 19, 2010 at 4:40 am
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!
July 19, 2010 at 4:41 am
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.
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply