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 1234»»»

VARCHAR datatype Expand / Collapse
Author
Message
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
Posted Saturday, July 17, 2010 4:38 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:22 PM
Points: 2,671, Visits: 787
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
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: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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
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: Wednesday, July 30, 2014 11:33 PM
Points: 361, Visits: 510
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
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: Wednesday, July 30, 2014 11:33 PM
Points: 361, Visits: 510
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
Posted Sunday, July 18, 2010 1:22 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:22 PM
Points: 2,671, Visits: 787
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
Posted Sunday, July 18, 2010 2:40 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
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
Posted Sunday, July 18, 2010 2:45 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
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
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: Wednesday, July 30, 2014 11:33 PM
Points: 361, Visits: 510
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
Posted Sunday, July 18, 2010 4:18 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:26 AM
Points: 2,953, Visits: 439
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 1234»»»

Permissions Expand / Collapse