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.
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.
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.
In this section we can see all the character string data types with more details.
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.
bytes
CAST
CONVERT
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.
char
DATALENGTH
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). " />
Variable-length, non-Unicode string data.
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.
GB
In below example we can see that the varchar field DATALENGTH is same as the data content length.
DECLARE @VarcharValue VARCHAR(10) SET @VarcharValue = 'SHEMEER' SELECT @VarcharValue AS '@VarcharValue', DATALENGTH(@VarcharValue) AS 'DATALENGTH', LEN(@VarcharValue) AS 'LEN'
@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).
Fixed-length Unicode string data.
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.
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.
N''
N'كيف حالك؟'
mailsub1 mailsub2 ??? ????? كيف حالك؟
Variable-length Unicode string data
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.
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;
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.
text
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.
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.
image
nvarchar(max)
varchar(max)
varbinary(max)
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?
nchar
varchar
nvarchar
Returns the ASCII code value of the leftmost character of a character expression. Click here to read more from msdn.
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 */
Converts an int ASCII code to a character. Click here to read more from msdn.
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
integer
NULL
SELECT CHAR(90) AS 'CHAR' /* Output CHAR Z */ SELECT CHAR(97) AS 'CHAR' /* Output CHAR a */ SELECT CHAR(256) AS 'CHAR' /* Output CHAR NULL */
Returns the Unicode character with the specified integer code, as defined by the Unicode standard. Click here to read more from msdn.
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.
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 */
NCHAR
Searches an expression for another expression and returns its starting position if found. Click here to read more from msdn.
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.
CHARINDEX
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 */
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.
CONCAT ( string_value1, string_value2 [, string_valueN ] )
Where string_value is a string value to concatenate to the other values.
DECLARE @StringValue varchar(100); SELECT CONCAT('String', ' Data', ' Types' , ' and Functions') AS 'CAPTION'; /* Output CAPTION -------- String Data Types and Functions */
Returns an integer value that indicates the difference between the SOUNDEX values of two character expressions. Click here to read more from msdn.
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.
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
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.
FORMAT
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.
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
Returns the left part of a character string with the specified number of characters. Click here to read more from msdn.
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.
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.'
Returns the right part of a character string with the specified number of characters. Click here to read more from msdn.
RIGHT ( character_expression , integer_expression )
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..
Returns the number of characters of the specified string expression, excluding trailing blanks. Click here to read more from msdn.
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.
SELECT LEN('SHEMEER') -- Returns 7
Returns a character expression after converting uppercase character data to lowercase. Click here to read more from msdn.
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.
SELECT LOWER('SHEMEER') -- Returns shemeer
Returns a character expression with lowercase character data converted to uppercase. Click here to read more from msdn.
UPPER ( character_expression )
SELECT UPPER('shemeer') -- Returns SHEMEER
Returns a character expression after it removes leading blanks. Click here to read more from msdn.
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.
The following example uses LTRIM to remove leading spaces from a character string.
SELECT LTRIM(' CODEPROJECT ') -- Returns 'CODEPROJECT '
Returns a character string after truncating all trailing blanks. Click here to read more from msdn.
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.
The following example uses RTRIM to remove trailing spaces from a character string.
SELECT RTRIM(' CODEPROJECT ') -- Returns ' CODEPROJECT'
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.
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.
SELECT PATINDEX('%those%', 'CODEPROJECT for those who code'); -- Returns 17
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.
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.
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.
Replaces all occurrences of a specified string value with another string value. Click here to read more from msdn.
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.
SELECT REPLACE('String Functions','String','Time'); -- Returns 'Time Functions'
Repeats a string value a specified number of times. Click here to read more from msdn.
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.
SELECT REPLICATE('0', 3) -- Returns 000 SELECT REPLICATE('0', -1) -- Returns NULL
Returns the reverse order of a string value. Click here to read more from msdn.
REVERSE ( string_expression )
string_expression is an expression of a string or binary data type.
SELECT REVERSE('CODEPROJECT') -- Returns 'TCEJORPEDOC'
Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings. Click here to read more from msdn.
SOUNDEX
SOUNDEX ( character_expression )
where character_expression is an alphanumeric expression of character data .
SELECT SOUNDEX ('shy'), SOUNDEX ('shi'); -- Returns 'S000
Returns a string of repeated spaces. Click here to read more from msdn.
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.
null
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.
REPLICATE
SPACE
Returns character data converted from numeric data. Click here to read more from msdn.
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.
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.
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.
STUFF
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.
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
Returns part of a character, binary, text, or image expression. Click here to read more from msdn.
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.
SELECT SUBSTRING('CODE PROJECT FOR THOSE WHO CODE', 6, 7); -- Returns 'PROJECT'
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.
UNICODE ( 'ncharacter_expression' )
Where ncharacter_expression is an nchar or nvarchar expression. .
I have specified all reference as a 'read more' link in all items, apart from that the main references are given below,
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.
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.
