String Data Types and Functions - SQL Server (2000, 2005, 2008, 2008 R2, 2012)

• Download Scripts.zip - 5.7 KB

• Introduction
• String Data Types - Quick Reference
• String Functions - Quick Reference
• String Data Types
• ->char
• ->varchar
• ->text 
• ->nchar
• ->nvarchar
• ->ntext
• Which one to use? When?
• String Functions
• -> ASCII 
• -> CHAR 
• -> NCHAR 
• -> UNICODE
• -> CHARINDEX
• -> PATINDEX
• -> SOUNDEX
• -> DIFFERENCE
• -> FORMAT
• -> LEFT
• -> RIGHT
• -> LEN
• -> LOWER
• -> UPPER
• -> LTRIM
• -> RTRIM
• -> STR
• -> QUOTENAME
• -> REPLICATE
• -> SPACE
• -> REPLACE
• -> REVERSE
• -> STUFF
• -> CONCAT
• -> SUBSTRING
• References 
• Summary

Introduction

Character string data types are used to store text values and Character String Functions are used to perform an operation on a string input value and return a string or numeric value.

In this article we will go through all character string data types and functions with sample query. If you want more info on any items then click on the 'read more details' of that section and that link will take you to the MSDN site. All working scripts are attached with this article. 

I have put my time and efforts on all of my articles, Please don't forget to mark your votes, suggestions and feedback to improve the quality of this and upcoming articles. Happy reading.

Quick Reference - String Data Types

The below table will list down all the string data types with short-description. Click on specific data type to read more. 

All above mentioned data types are expalined in another section. 

Quick Reference - String Functions

The below table will list down all the string functions with short-description. Click on specific function name to read more.  

All built-in string functions are deterministic. This means they return the same value any time they are called with a specific set of input values.

All above mentioned functions are expalined in another section. 

String Data Types

In this section we can see all the character string data types with more details.   

char

Fixed-length, non-Unicode string data.

Syntax :-

char [ ( n ) ] 

Where n defines the string length and must be a value from 1 through 8,000. The storage size is n bytes. When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30. Click here to read more from msdn.

If you see the below example you can see that the defined char variable size is 10 and it contains only 7 characters and remaining part filled with 3 blank space that's why the DATALENGTH returns 10.

Example :-

DECLARE @CharValue CHAR(10) 
 
SET @CharValue = 'SHEMEER' 
 
SELECT @CharValue             AS '@CharValue', 
       DATALENGTH(@CharValue) AS 'DATALENGTH', 
       LEN(@CharValue)        AS 'LEN' 

Output :-

@CharValue    DATALENGTH    LEN
SHEMEER       10    7  

I tried adding a column with char(MAX). " />  

varchar

Variable-length, non-Unicode string data.

Syntax :-

varchar [ ( n | max ) ] 

Where n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes. When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30. Click here to read more from msdn.

In below example we can see that the varchar field DATALENGTH is same as the data content length.

Example :-

DECLARE @VarcharValue VARCHAR(10) 
 
SET @VarcharValue = 'SHEMEER' 
 
SELECT @VarcharValue             AS '@VarcharValue', 
       DATALENGTH(@VarcharValue) AS 'DATALENGTH', 
       LEN(@VarcharValue)        AS 'LEN'  

Output :-

@VarcharValue    DATALENGTH    LEN
SHEMEER    7    7 

I tried adding a column with varchar with varcharmax length, but it thrown error as it wont get converted automatically to varchar(max). 

nchar

Fixed-length Unicode string data.

Syntax :-

nchar [ ( n ) ]

Where n defines the string length and must be a value from 1 through 4,000. The storage size is two times n bytes. When the collation code page uses double-byte characters, the storage size is still n bytes. Depending on the string, the storage size of n bytes can be less than the value specified for n. When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30. Click here to read more from msdn.

Example :-

CREATE TABLE #t_demo 
  ( 
     mailsub1 CHAR(30), 
     mailsub2 NCHAR(30) 
  ); 
 
INSERT INTO #t_demo 
            (mailsub1, 
             mailsub2) 
VALUES      ( N'كيف حالك؟', 
              N'كيف حالك؟'); 
 
SELECT * 
FROM   #t_demo; 
 
GO 
 
DROP TABLE #t_demo;  

To mark a string constant as Unicode, use the format N'', for example, @mail_sub = N'كيف حالك؟' . Click here to read more about Unicode data from msdn.

Output :-

mailsub1    mailsub2
??? ?????       كيف حالك؟                      

nvarchar

Variable-length Unicode string data

Syntax :-

nvarchar [ ( n | max ) ] 

Where n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30. Click here to read more from msdn.

Example :-

CREATE TABLE #t_demo 
  ( 
     mailsub1 VARCHAR(30), 
     mailsub2 NVARCHAR(30) 
  ); 
 
INSERT INTO #t_demo 
            (mailsub1, 
             mailsub2) 
VALUES      ( N'كيف حالك؟', 
              N'كيف حالك؟'); 
 
SELECT * 
FROM   #t_demo; 
 
GO 
 
DROP TABLE #t_demo; 

Output :-

mailsub1    mailsub2
??? ?????    كيف حالك؟ 

text

Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647). When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.

Syntax :-

text 

ntext

Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823) bytes. Storage size, in bytes, is two times the string length that is entered.

Syntax :-

ntext 

Note : ntext , text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Which string data type to use? When?

From previous section we have understood there are couple of data types associated with character string.  After going through the different data types we all get a question in mind, which one to use and when? 

• Use char when the sizes of the column data entries are consistent.
• Use nchar when working with multiple languages (Unicode characters) and the sizes of the column data entries are probably going to be similar.
• Use varchar when the sizes of the column data entries vary considerably.
• Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.
• Use nvarchar when when working with multiple languages (Unicode characters) and the sizes of the column data entries are probably going to vary considerably.
• Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 4,000 bytes.
• Avoid using text and ntext data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

String Functions

ASCII 

Returns the ASCII code value of the leftmost character of a character expression. Click here to read more from msdn. 

Syntax :-

ASCII ( character_expression )

Where character_expression is an expression of the type char or varchar.

Sample :-

DECLARE @CharValue char(5)
SET @CharValue = 'C'
SELECT @CharValue AS 'CHAR', ASCII(@CharValue) AS 'ASCII'
 
/* Output :-
CHAR    ASCII
C        67
*/
 
--In below code also ASCII function will only consider the leftmost character.
DECLARE @CharValue char(5)
SET @CharValue = 'CODE'
SELECT @CharValue AS 'CHAR', ASCII(@CharValue) AS 'ASCII'
 
/* Output
CHAR    ASCII
CODE     67 */ 

CHAR 

Converts an int ASCII code to a character. Click here to read more from msdn.  

Syntax :-

CHAR ( integer_expression )

Where integer_expression is an integer from 0 through 255. NULL is returned if the integer expression is not in this range. CHAR can be used to insert control characters ( eg:- char(9) - Tab, char(10) - Line feed, char(13) - Carriage return etc...) into character strings

Sample :-

SELECT CHAR(90) AS 'CHAR'
 
/* Output
CHAR
Z
*/
 
SELECT CHAR(97) AS 'CHAR'
 
/* Output
CHAR
a
*/
 
SELECT CHAR(256) AS 'CHAR'
 
/* Output
CHAR
NULL
*/ 

NCHAR

Returns the Unicode character with the specified integer code, as defined by the Unicode standard. Click here to read more from msdn.  

Syntax :-

NCHAR ( integer_expression )  

If the collation of the database does not contain the supplementary character (SC) flag then integer_expression is a positive whole number from 0 through 65535 else 0 through 1114111.If a value outside this range is specified, NULL is returned.

Sample :-

DECLARE @arabicletter nvarchar(10) = N'ك';
SELECT NCHAR(UNICODE(@arabicletter)) AS 'NCHAR', CHAR(UNICODE(@arabicletter)) AS 'CHAR', UNICODE(@arabicletter) AS 'UNICODE'
 
/* Output
NCHAR    CHAR    UNICODE
ك    NULL    1603
*/

CHARINDEX

Searches an expression for another expression and returns its starting position if found. Click here to read more from msdn.  

Syntax :- 

CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )  

Where 'expressionToFind' is a character expression that contains the sequence to be found and 'expressionToSearch' is a character expression to be searched. The optional parameter 'start_location' is an integer or bigint expression at which the search starts.

'expressionToFind' is limited to 8000 characters. If 'start_location' is not specified, is a negative number, or is 0, the search starts at the beginning of expressionToSearch. If either expressionToFind or expressionToSearch is NULL, CHARINDEX returns NULL. If expressionToFind is not found within expressionToSearch, CHARINDEX returns 0. 

Sample :-  

DECLARE @StringValue varchar(100);
 
SELECT @StringValue = 'String Data Types and Functions'; 
SELECT CHARINDEX('Types', @StringValue) AS 'CHARINDEX'; --Searching for a existent expression
SELECT CHARINDEX('Data', @StringValue, 6) AS 'CHARINDEX'; --Searching from a specific position
SELECT CHARINDEX('Shemeer', @StringValue) AS 'CHARINDEX'; --Searching for a nonexistent expression

/*
The output shows respetively
CHARINDEX
---------
13
8
0
*/ 

CONCAT

Returns a string that is the result of concatenating two or more string values. CONCAT requires a minimum of two input values. Click here to read more from msdn.  Click here to read more from msdn.  

Syntax :-  

CONCAT ( string_value1, string_value2 [, string_valueN ] )

Where string_value is a string value to concatenate to the other values. 

Sample :-

DECLARE @StringValue varchar(100);
 
SELECT CONCAT('String', ' Data', ' Types' , ' and Functions') AS 'CAPTION'; 
 
/*
Output
 
CAPTION
--------
String Data Types and Functions
*/ 

DIFFERENCE

Returns an integer value that indicates the difference between the SOUNDEX values of two character expressions. Click here to read more from msdn.  

Syntax :-

DIFFERENCE ( character_expression , character_expression ) 

Where character_expression is an alphanumeric expression of character data. character_expression can be a constant, variable, or column. The return value ranges from 0 through 4: 0 indicates weak or no similarity, and 4 indicates strong similarity or the same values.

Sample :-

SELECT DIFFERENCE('ABC','ABCD'); -- Returns 3, means more similarity 
SELECT DIFFERENCE('ABC','ABC'); -- Returns 4, means strong similarity or same
SELECT DIFFERENCE('ABC','123'); -- Returns 0, means no similarity   

FORMAT

Returns a value formatted with the specified format and optional culture. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. Click here to read more from msdn.  

Syntax :-

FORMAT ( value, format [, culture ] ) 

Where value is an expression of a supported data type to format. The format argument must contain a valid .NET Framework format string, either as a standard format string or as a pattern of custom characters for dates and numeric values. Composite formatting is not supported. format argument is in an nvarchar format pattern. 

Sample :-

DECLARE @d DATETIME = '10/01/2011';
SELECT FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result' -- Return 2011/10/1

SELECT FORMAT(101.2, 'C', 'en-us') AS 'Currency Format' -- Returns $101.20 

LEFT

Returns the left part of a character string with the specified number of characters. Click here to read more from msdn.  

Syntax :-

LEFT ( character_expression , integer_expression )

Where character_expression can be expression, constant, variable, or column of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert character_expression.  integer_expression is a positive integer that specifies how many characters of the character_expression will be returned. 

Sample :-

SELECT LEFT('SHEMEER',3) -- Returns SHE

SELECT LEFT('SHEMEER',100) -- Returns SHEMEER

--SELECT LEFT('SHEMEER',-1) -- Error 'Invalid length parameter passed to the left function.' 

RIGHT

Returns the right part of a character string with the specified number of characters. Click here to read more from msdn.  

Syntax :-

RIGHT ( character_expression , integer_expression ) 

Where character_expression can be expression, constant, variable, or column of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert character_expression.  integer_expression is a positive integer that specifies how many characters of the character_expression will be returned.

Sample :-

SELECT RIGHT('SHEMEER',3) -- Returns EER

SELECT RIGHT('SHEMEER',100) -- Returns SHEMEER

--SELECT RIGHT('SHEMEER',-1) -- Error 'Invalid length parameter passed to the right function.. 

LEN

Returns the number of characters of the specified string expression, excluding trailing blanks. Click here to read more from msdn.  

Syntax :-

LEN ( string_expression )

string_expression is the string expression to be evaluated. string_expression can be a constant, variable, or column of either character or binary data. 

Sample :-

SELECT LEN('SHEMEER') -- Returns 7 

LOWER

Returns a character expression after converting uppercase character data to lowercase. Click here to read more from msdn.  

Syntax :-

LOWER ( character_expression ) 

Where character_expression can be expression, constant, variable, or column and must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression. 

Sample :- 

SELECT LOWER('SHEMEER') -- Returns shemeer  

UPPER

Returns a character expression with lowercase character data converted to uppercase. Click here to read more from msdn.  

Syntax :- 

UPPER ( character_expression ) 

Where character_expression can be expression, constant, variable, or column and must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.

Sample :-

SELECT UPPER('shemeer') -- Returns SHEMEER 

LTRIM

Returns a character expression after it removes leading blanks. Click here to read more from msdn.  

Syntax :-

LTRIM ( character_expression )

Where character_expression can be expression, constant, variable, or column of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert character_expression.

Sample :-

The following example uses LTRIM to remove leading spaces from a character string. 

SELECT LTRIM('  CODEPROJECT  ') -- Returns 'CODEPROJECT  ' 

RTRIM

Returns a character string after truncating all trailing blanks. Click here to read more from msdn.  

Syntax :-

RTRIM ( character_expression )

character_expression can be expression, constant, variable, or column of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert character_expression.

Sample :- 

The following example uses RTRIM to remove trailing spaces from a character string. 

SELECT RTRIM('  CODEPROJECT  ') -- Returns '  CODEPROJECT'  

PATINDEX

Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types. We can say this function is equivalent to CHARINDEX +  PATTERN(Wildcard Search) . Click here to read more from msdn.  

Syntax :-

PATINDEX ( '%pattern%' , expression )

pattern is a character expression that contains the sequence to be found and limited to 8000 characters. expression is an expression, typically a column that is searched for the specified pattern.

Sample :-

SELECT PATINDEX('%those%', 'CODEPROJECT for those who code'); -- Returns 17 

QUOTENAME

Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier. Click here to read more from msdn.  

Syntax :-

QUOTENAME ( 'character_string' [ , 'quote_character' ] )

character_string is a string of Unicode character data. character_string is sysname and is limited to 128 characters. Inputs greater than 128 characters return NULL. quote_character is a one-character string to use as the delimiter. Can be a single quotation mark ( ' ), a left or right bracket ( [ ] ), or a double quotation mark ( " ). If quote_character is not specified, brackets are used. 

Sample :-

SELECT QUOTENAME('DESC') -- Returns '[DESC]'
SELECT QUOTENAME('sql[]string') -- Returns '[sql[]]string]' , Notice that the right bracket in the string sql[]string is doubled to indicate an escape character. 

REPLACE

Replaces all occurrences of a specified string value with another string value.  Click here to read more from msdn.  

Syntax :-

REPLACE ( string_expression , string_pattern , string_replacement )

Where string_expression is the string expression to be searched, string_pattern is the substring to be found, string_replacement is the replacement string. 

Sample :- 

SELECT REPLACE('String Functions','String','Time'); -- Returns 'Time Functions' 

REPLICATE

Repeats a string value a specified number of times. Click here to read more from msdn.  

Syntax :- 

REPLICATE ( string_expression ,integer_expression ) 

Where string_expression is an expression of a character string or binary data type. integer_expression is an expression of any integer type, including bigint. If integer_expression is negative, NULL is returned.

Sample :-

SELECT REPLICATE('0', 3) -- Returns 000
SELECT REPLICATE('0', -1) -- Returns NULL 

REVERSE

Returns the reverse order of a string value. Click here to read more from msdn.  

Syntax :-

REVERSE ( string_expression ) 

string_expression is an expression of a string or binary data type. 

Sample :- 

SELECT REVERSE('CODEPROJECT') -- Returns 'TCEJORPEDOC' 

SOUNDEX 

Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings. Click here to read more from msdn.  

Syntax :- 

SOUNDEX ( character_expression ) 

where character_expression is an alphanumeric expression of character data .

Sample :- 

SELECT SOUNDEX ('shy'), SOUNDEX ('shi'); -- Returns 'S000 

SPACE

Returns a string of repeated spaces. Click here to read more from msdn.  

Syntax :-

SPACE ( integer_expression )

Where integer_expression is a positive integer that indicates the number of spaces. If integer_expression is negative, a null string is returned. 

Sample :-

SELECT CONCAT('CODE',SPACE(2),'PROJCET') -- Returns 'CODE  PROJCET' 

To include spaces in Unicode data, or to return more than 8000 character spaces, use REPLICATE instead of SPACE. 

STR

Returns character data converted from numeric data. Click here to read more from msdn.  

Syntax :-

STR ( float_expression [ , length [ , decimal ] ] )

Where float_expression is an expression of approximate numeric (float) data type with a decimal point. length is the total length. This includes decimal point, sign, digits, and spaces. The default is 10. decimal is the number of places to the right of the decimal point. decimal must be less than or equal to 16. If decimal is more than 16 then the result is truncated to sixteen places to the right of the decimal point. 

Sample :-

SELECT 'SL NO' + 1 -- Returns Error
--'Conversion failed when converting the varchar value 'SL NO' to data type int.'

SELECT 'SL NO' + STR(1,2) -- Returns 'SL NO 1'

SELECT STR (FLOOR (123.45), 8, 3); -- Returns ' 123.000'

SELECT STR(123.45, 2, 2); -- Returns '**'
--When the expression exceeds the specified length, the string returns ** for the specified length. 

STUFF 

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position. Click here to read more from msdn.  

Syntax :-

STUFF ( character_expression , start , length , replaceWith_expression )  

Where character_expression is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data. start is an integer value that specifies the location to start deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned. start can be of type bigint. length is an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression. length can be of type bigint. replaceWith_expression is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data. This expression will replace length characters of character_expression beginning at start. 

Sample :-

SELECT STUFF('CODEFOR THOSE WHO CODE', 5, 0, ' PROJECT '); 
-- Returns 'CODE PROJECT FOR THOSE WHO CODE'

--Where 5 is the position where we want to insert ' PROJECT ', 
--and 0 represnts the number of characters to be replaced from the position 5 

SUBSTRING

Returns part of a character, binary, text, or image expression. Click here to read more from msdn.  

Syntax :-

SUBSTRING ( value_expression , start_expression , length_expression )

Where value_expression is a character, binary, text, ntext, or image expression. start_expression is an integer or bigint expression that specifies where the returned characters start. length_expression is a positive integer or bigint expression that specifies how many characters of the expression will be returned.

Sample :-

SELECT SUBSTRING('CODE PROJECT FOR THOSE WHO CODE', 6, 7); 
-- Returns 'PROJECT' 

UNICODE

Returns the integer value, as defined by the Unicode standard, for the first character of the input expression. Click here to read more from msdn.  

Syntax :-

UNICODE ( 'ncharacter_expression' ) 

Where ncharacter_expression is an nchar or nvarchar expression. . 

Sample :-

DECLARE @arabicletter nvarchar(10) = N'ك';
SELECT NCHAR(UNICODE(@arabicletter)) AS 'NCHAR', CHAR(UNICODE(@arabicletter)) AS 'CHAR', UNICODE(@arabicletter) AS 'UNICODE'
 
/* Output
NCHAR    CHAR    UNICODE
ك    NULL    1603
*/  

References

I have specified all reference as a 'read more' link in all items, apart from that the main references are given below,

• MSDN - Data Types (Transact- SQL)
• MSDN - String Functions (Transact-SQL)

Summary

In this article I have explained String Data Types and Functions of SQL Server (2000 and above) from a beginner's perspective. If I have missed anything or need any change in definition then please let me know. I hope you have enjoyed this article and got some value addition to your knowledge.

You might be interested in the below articles.

• Functions that helps to convert date and time values to and from string literals and other date and time formats.
• Some Interesting Date and Time Questions.
• Date and Time Data Types and Functions - SQL Server.
• .NET Framework Cultures with Culture Specific Formats and Mapping with SQL Server Language. 

I have put my time and efforts on all of my articles, Please don't forget to mark your votes, suggestions and feedback to improve the quality of this and upcoming articles. Thanks for reading.