Character Data Types



SQL Sever supports two kinds of character data types:


Regular: CHAR, VARCHAR
   One byte stored per character 
   Only 256 possible characters - limits language support

Unicode: NCHAR, NVARCHAR
   Two bytes stored per character
    65k characters represented - multiple language support
    Precede characters with 'N' (National)

TEXT, NTEXT deprecated
   Use VARCHAR (MAX), NVARCHAR(MAX) instead

  • CHAR, NCHAR are fixed length
  • VARCHAR, NVARCHAR are variable length
  • Character data is delimited with single quotes




 Data Type

                 Range

                 Storage

CHAR(n),NCHAR(n)

1 - 8000 characters 
n bytes, padded 
2*n bytes, padded

VARCHAR(n), NVARCHAR(n)

1 - 8000 characters 
n+2 bytes
(2*n) +2 bytes 
VARCHAR(MAX), NVARCHAR(MAX)

1 - 2 ^ 31 - 1 characters  

Actual length +2

String concatenation


SQL Server uses the + (plus) sign to concatenate characters:
Concatenating a value with a NULL returns a NULL


SELECT  SchoolEntityID, FirstName, LastName,
FirstName + N'  '  + LastName AS FullName
FROM  Person.Person

SQL Sever 2012 introduces CONCAT() function
Converts NULL to empty string before concatenation

SELECT  AddressLine1, City, StateProvinceID,
CONCAT (AddressLine1, ',' + City, ',' + PostalCode)  AS
Location
FROM Person.Addres


Character string functions


Common functions that modify character strings


        Function

                      Syntax

               Remarks

SUBSTRING()
SUBSTRING (expression, start, length)

Returns part of an expression

LEFT(), RIGHT()
LEFT (expression, integer_value) RIGHT (expression,integer_value)
LEFT() returns left part of string up to integer_value. RIGHT() returns right part of string.
LEN(), DATALENGTH()

LEN (string_expression) DATALENGTH (expression)
LEN() returns the number of characters of the specified string expression, excluding trailing blanks. DATALENGHT() returns the number bytes used.

CHARINDEX()
CHARINDEX (expressionToFind, expressionToSearch)
Searches an expression for another expression and returns its starting position if found. Optional start position.

REPLACE()
REPLACE (string_expression, string_pattern, string_replacement)
Replaces all occurrences of a specified string value with another string value. 

UPPER(), LOWER()
UPPER (character_expression)
LOWER (character_expression)
UPPER() returns a character expression with lowercase character data converted to uppercase. LOWER() converts uppercase to lowercase.