CODEWITHSUNDEEP
sql-servercasting
user1672097
user1672097

Reputation: 351

Casting Varchar to fixed length

Is there any issue with the following SQL? Here no length has been used while converting to varchar.

SELECT CAST('abc' AS varchar)

Should I use 

 SELECT CAST('abc' AS varchar(3))

Upvotes: 3

Views: 7791

Answers (3)

Mangoose
Mangoose

Reputation: 922

If you do not specify varchar(n) then n will be assumed to be 30. For example: If we run the query below, we get 30 chars of output.

CAST('01234567890123456789012345678901234567890123456789012345678901234567890123456789' as varchar)

Also have a look at the following msdn article for more clarity.

Upvotes: 4

Khan
Khan

Reputation: 18162

As long as your string does not exceed 30 characters, you're fine.
SQL Server will try to cast to the default length which is 30 characters.

If you try to cast a string that exceeds this length, your string will be truncated to 30 characters.

Example:

DECLARE @Example VARCHAR(35) = 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' -- 35 Characters
SELECT LEN(cast(@Example as varchar)) AS [Result]

Result
30

Upvotes: 1

asafrob
asafrob

Reputation: 1858

If you will use the cast to insert the data to a column that has a fixed length the second statement will help you avoid the "string or binary data would be truncated" error by truncating the data

SQLException : String or binary data would be truncated

Upvotes: 0

Related Questions