Casting Varchar to fixed length

Question

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))

you should **NEVER** use just `varchar`, always specify a length. Besides being just lazy, you should always size fields properly, it is just one of the ways the database protects the data's integrity. — KM., Jul 23 '13 at 17:57

score 4 · Answer 1 · edited Jul 24 '13 at 07:45

4

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.

edited Jul 24 '13 at 07:45

Micha Wiedenmann

19,979
21
92
137

answered Jul 23 '13 at 17:33

Mangoose

922
1
9
17

Khan · Answer 2 · 2013-07-23T17:32:47.323

1

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

edited Jul 23 '13 at 17:32

answered Jul 23 '13 at 17:26

Khan

17,904
5
47
59

2

looks like there's an automatic truncation, rather than an error: http://www.sqlfiddle.com/#!3/d41d8/17670 – Cristian Lupascu Jul 23 '13 at 17:28
Edited, thank you. I referenced an incorrect article for the result. – Khan Jul 23 '13 at 17:33

score 0 · Answer 3 · edited May 23 '17 at 11:57

0

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

edited May 23 '17 at 11:57

Community

1
1

answered Jul 23 '13 at 17:28

asafrob

1,838
13
16

Casting Varchar to fixed length

3 Answers3