CAST surprise - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

CAST surprise

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-03 : 23:03:29

I've been in the habbit of using

CAST(column as VARCHAR)

a lot without specifying a length.

It isn't mentioned in BOL anywhere (that I could see), but it appears to default to VARCHAR(20).

Anything past that is truncated.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-12-03 : 23:23:57

I get something different....


DECLARE @Text VARCHAR(45), @TextNone VARCHAR
SET @Text = '1234567890-1234567890-123456789'
SET @TextNone = @Text
SELECT @Text, LEN(@Text), CAST(@Text as VARCHAR), LEN(CAST(@Text as VARCHAR)), @TextNone

I get one less character from the CAST...
Declaring it without size yields a single character..

@@Version = 8.00.760

DavidM

"SQL-3 is an abomination.."

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-03 : 23:34:47

My test:

Print cast('01234567890123456789012345678901234567890123456789' as varchar)

results in:
012345678901234567890123456789

So I suppose it's defaulting to VARCHAR(30)

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-12-03 : 23:41:37

I'm getting varchar(30) on both the developer edition on my laptop and a standard edition on a Win2k server.

Damian

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-12-03 : 23:44:41

But I see the what David is getting in his example too. If you declare a varchar without a length it defaults to 1.

Damian

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-12-04 : 00:03:15

The CHAR, NCHAR & NVARCHAR, BINARY, VARBINARY data type exhibits the same results - Length 30 when cast. Length 1 when declared..At least they are consistent.

And Sam.. it is IN BOL..

quote:

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources