Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Greg Goodge » VARCHAR datatype
Add to briefcase
38 posts, Page 1 of 4
1234»»»

VARCHAR datatype Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
JestersGrind
Posted Saturday, July 17, 2010 12:17 PM


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: Tuesday, May 14, 2013 1:42 PM
Points: 3,487, Visits: 1,581
Comments posted to this topic are about the item VARCHAR datatype


Post #954419
Jamie Longstreet-481950
Posted Saturday, July 17, 2010 4:38 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:41 AM
Points: 2,440, Visits: 713
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
Post #954454
SanjayAttray
Posted Sunday, July 18, 2010 7:25 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: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554
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.
Post #954492
hrvoje.piasevoli
Posted Sunday, July 18, 2010 12:51 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 07, 2012 9:27 AM
Points: 361, Visits: 507
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
Post #954529
hrvoje.piasevoli
Posted Sunday, July 18, 2010 12:56 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 07, 2012 9:27 AM
Points: 361, Visits: 507
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
Post #954531
Jamie Longstreet-481950
Posted Sunday, July 18, 2010 1:22 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:41 AM
Points: 2,440, Visits: 713
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
Post #954537
UMG Developer
Posted Sunday, July 18, 2010 2:40 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163, Visits: 2,148
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!
Post #954543
UMG Developer
Posted Sunday, July 18, 2010 2:45 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163, Visits: 2,148
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.
Post #954545
hrvoje.piasevoli
Posted Sunday, July 18, 2010 3:11 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 07, 2012 9:27 AM
Points: 361, Visits: 507
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
Post #954548
Iggy-SQL
Posted Sunday, July 18, 2010 4:18 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:02 PM
Points: 2,859, Visits: 405
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
Post #954554
« Prev Topic | Next Topic »

Add to briefcase
38 posts, Page 1 of 4
1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.