SQL

String data types store character data. Name, address and descriptive text are all examples of character data. The legal values are usually the entire range of alphanumeric characters, including punctuation and sometimes special characters.

By default, string data is delimited in single quotes: 'this is a text string'. A string literal is a chunk of text data, inside delimiters. Some databases offer additional delimiters such as [ or {. Some databases will even let you define your own delimiters. Having alternative delimiters is beneficial when you want to include the default delimiter within a string, such as:

v_char_field := [This is a single quote: ' and I don't get an error.];

Or:

v_char_field := 'This is a single quote: '' and I don''t get an error.';

I find the first example easier to read than the second.

There are two primary types of string data types: fixed length and variable length. A fixed length string is declared to be a certain size and will always be that size. If the data stored in the column or variable is less than the full length of the column or variable, the data will be padded with spaces to be the maximum length.

A variable length string may or may not be a declared maximum size. Data stored within the column or variable is no larger than it needs to be and is not padded with spaces unless the user or application pads it manually.

The classic fixed length string data type is the CHAR data type. A CHAR defaults to a single character in many databases. Parenthesis is used to declare a length: CHAR(10). If a column is declared as CHAR(10) and you store the string 'Lewis' in it, the database will save 'Lewis '.

CHARACTER is synonymous for CHAR.

Variable data types come by many names: VARCHAR, VARCHAR2, TEXT, CLOB, and many more. Like the CHAR (and any other data type) the length is declared within parenthesis: VARCHAR2(10). If a column is declared as VARCHAR2(10) and you store the string 'Lewis' in it, the database will save 'Lewis'; no padding is added. If however, you were to store 'Lewis ', the database would store it that way. It would not add any padding, but it would not remove any either.

When comparing strings, it is important to understand the differences between fixed and variable. Each vendor can implement this differently but in general, if a comparison of two strings uses two CHAR variables, they are padded to the same size, even if the variables are declared as different lengths.

For example, if:

Var1 CHAR(5);

Var2 CHAR(10);

Var1 := 'Lewis';

Var2 := 'Lewis ';

Then Var1 = Var2 is true, and

Var1 CHAR(5);

Var2 CHAR(10);

Var1 := 'Lewis';

Var2 := 'Lewis';

Var1 = Var2 is also true.

If one of the variables is declared as a variable length data type, the padding does not happen:

If:

Var1 CHAR(10);

Var2 VARCHAR(10);

Var1 := 'Lewis';

Var2 := 'Lewis ';

Then Var1 = Var2 is true, but:

Var1 CHAR(10);

Var2 VARCHAR(10);

Var1 := 'Lewis';

Var2 := 'Lewis';

Var1 = Var2 is false.

If we make the two variables the same size and fill them both up:

Var1 CHAR(5);

Var2 VARCHAR(5);

Var1 := 'Lewis';

Var2 := 'Lewis';

Then Var1 = Var2 is true.

Topic: String Numeric Date Boolean

Topics:

• Intro
• SQL
• DML
• DDL
• Data Types
• Expressions and Operators
• Common SQL Functions