loading..
Русский    English
19:17

CHAR and VARCHAR page 1

Recently I happened to find an error in the decision, which included the conversion:

  1. CAST(model AS VARCHAR)

Those who have studied the scheme "Computers", think about the absurdity of the type conversion in the same type (the column ‘model’ is defined as VARCHAR (50)). However, it is this conversion, and made the request wrong.

The fact is that, if the type size is not specified when converting, then SQL Server is set to defaults, which for VARCHAR are 30. Thus, if the converted string has larger size, then cut off all the characters except for the first 30. Of course, any errors will not occur. Just on the "long" model numbers the proposed decision and gave the wrong result. As they say in such cases, read the documentation. However, it is interesting that on this occasion Standard said?

In according to standard, if the size is not counted for CHAR and VARCHAR types, it means CHAR(1) and VARCHAR(1) respectively. Let's check DBMS available to me, according to the standard: SQL Server, MySQL, PostgreSQL.

Here there are two aspects:

1. Type conversion

2. Using types CHAR / VARCHAR to describe the schema (DDL)

Let's start with type conversion.

SQL Server 2008

Console
Execute
  1. SELECT CAST('11111111112222222222333333333344444444445555555555' AS CHAR) chr,
  2. CAST('11111111112222222222333333333344444444445555555555' AS VARCHAR) vchr

The result:

chr vchr
111111111122222222223333333333 111111111122222222223333333333

That is both a character types truncated to defaults, which are 30. No error messages occur that, in fact, meet the standard.

PostgreSQL 8.3

  1. SELECT CAST('11111111112222222222333333333344444444445555555555' AS CHAR) AS chr,
  2.  CAST('11111111112222222222333333333344444444445555555555' AS VARCHAR) AS vchr


chr vchr
1 11111111112222222222333333333344444444445555555555

There is a half-hearted compliance with standard, i.e. match it against the type CHAR. With regard to the type VARCHAR, then according to the documentation, if character varying is used without length specifier, the type accepts strings of any size, ie truncation does not occur.

MySQL 5.0

As the saying goes, the farther, the "curiouser". It appears that in MySQL, the conversion of type VARCHAR not supported at all. It remains to check only the conversion to CHAR:

  1. SELECT CAST('11111111112222222222333333333344444444445555555555' AS CHAR) chr


chr
11111111112222222222333333333344444444445555555555

Ie string is not truncated, while in the documentation I read: “When n is not specified when using the CAST and CONVERT functions, the default length is 30”.

Bookmark and Share
Pages 1 2
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100