SQL string Functions

SQL String Functions
String functions perform different operations on binary data, strings, and expressions. Such functions work on Char, VARCHAR, BINARY, and varbinary data types, as well as data types that can be implicitly converted to char or varchar. You can use String functions in the Select and WHERE clauses of a SELECT statement, as well as in an expression. The commonly used string functions are:
The character conversion function 1, ASCII () returns the ASCII value of the leftmost character of the character expression. In the ASCII () function, a string of pure numbers is not "enclosed", but a string with other characters must be used in ' surround ', or an error will occur.

2, CHAR () converts the ASCII code to a character. If you do not enter an ASCII value between 0 and 255, CHAR () returns NULL.

3, LOWER () and UPPER () LOWER () convert all strings to lowercase, UPPER () capitalizes all strings.

4. STR () converts numeric data to character data. STR (<float_expression> [, length[, <decimal>]) length specifies how long to return the string, and decimal specifies the number of decimal digits to return. If the length is not specified, the default value is ten, and the decimal default is 0. returns NULL when length or decimal is negative, when length is less than the number of digits to the left of the decimal point (including the sign bit), and the length is followed by length, then decimal; when the returned word The number of bits is less than length, and the left side complements the space.

second, go to the space function1. LTRIM () Remove the space at the head of the string.
2. RTRIM () Remove the space at the trailing end of the string.
Three, take the SUBSTRING function1.Left ()Left (&LT;CHARACTER_EXPRESSION&GT;, <integer_expression>)Returns the integer_expression character from the left of character_expression.
2. Right ()Right (&LT;CHARACTER_EXPRESSION&GT;, <integer_expression>)  return character_expression  right  integer_expression  characters. &NBSP;
3, SUBSTRING ()   SUBSTRING   (<expression>, <starting_ position>, length)   returns the part starting_ position  characters from the left of the string character the length character. &NBSP;
Four, string comparison function   1, CHARINDEX ()   returns the beginning of the occurrence of a specified substring in a string.   charindex  (< ' substring_expression ' >, <expression >)   where substring _expression  is the character expression to look for,expression  Can be a string or a column-name expression. If no substring is found, the 0  value is returned.   this function cannot be used for text  and image  data types.

2, PATINDEX () returns the beginning of the occurrence of a specified substring in a string. PATINDEX (< '%substring _expression% ';, <column_ name>) where the substring expression must have a percent semicolon "%" or the return value is 0. Unlike the CHARINDEX function, wildcard characters can be used in substrings of the PATINDEX function, and this function can be used with char, VARCHAR, and text data types.
The string manipulation function 1, QUOTENAME () returns a string enclosed by a specific character. QUOTENAME (< ' character_expression ' > [, quote_ character]) where Quote_ character the character used to enclose the string, the default value is "[]".

2, REPLICATE ()Returns a string that repeats character_expression the specified number of times.REPLICATE (character_expression integer_expression) returns null if the Integer_expression value is negative.
3, REVERSE ()Reverses the character order of the specified string.REVERSE (<character_expression>) where character_expression can be a string, a constant, or a column's value.
4. REPLACE ()Returns the string that was replaced by the specified substring.REPLACE (&LT;STRING_EXPRESSION1&GT;, <string_expression2>, <string_expression3>) with String_expression3 Replace the substring string_expression2 in the string_expression1.
5, Space () returns a blank string with the specified length. SPACE (<integer_expression>) returns null if the Integer_expression value is negative.
6, STUFF () Specifies the position, length of the substring with another substring substitution string. STUFF (<character_expression1>, <start_ Position>, <length>,<character_expression2>) A null value is returned if the starting position is negative or the length value is negative, or the starting position is greater than the length of character_expression1. if length is greater than start_ position in Character_expression1 to the right length, Character_expression1 retains only the first character.

VI. data type conversion Functions1. CAST ()CAST (<expression> as <data_ type>[length])
2. CONVERT ()CONVERT (<data_ type> [length], <expression> [, Style])&NBSP;
1) data_type the data type defined for the Sql server system, the user-defined data type cannot be used here.   2) length is used to specify the lengths of the data, and the default value is 30.   3) converts a char or varchar type to an integer type such as int or samllint, and the result must be a numeric value with a plus or minus sign.   4) the text type is converted to char or varchar type up to 8,000 characters, that is, the char or varchar data type is the maximum length.   5) The data stored by the image type is converted to binary or varbinary type, up to 8,000 characters.   6) converts an integer value to a money or smallmoney type, processed in the currency unit of the defined country, such as renminbi, US dollar, pound sterling, and so on. 7) A bit type conversion converts a non-0 value to 1 and is still stored as a bit type.   8) attempts to convert to a different length of data type, truncates the conversion value and displays "+" after the value is converted to identify that this truncation has occurred.   9) The style  option with the CONVERT ()   function displays the date and time in a different format. style  is the conversion style number that is provided by the sql server  system when converting datatime  and smalldatetime  data to strings, and different style numbers have different output formats. &NBSP;

Seven, date function 1, Day (date_expression) returns the date value in the Date_expression
2, month (date_expression) returns the months value in Date_expression
3, Year (date_expression) returns the years value in Date_expression
4, DATEADD () DATEADD (<datepart>, <number>, <date>) returns the new date that is generated by the specified date date plus the specified extra date interval number. The parameter "DatePart" takes the following values:

5, DATEDIFF ()DATEDIFF (&LT;DATEPART&GT;, <date1>, <date2>)
Returns the DATEPART difference between the two specified dates, that is, the date2 exceeds the value of date1, and the resulting value is an integer value with a positive sign.

6, Datename ()&NBSP;
datename  (<datepart>, <date>) &NBSP;
returns the specified part of the date as a string. specified by datepart . &NBSP;

7, DATEPART ()   datepart  (<datepart>, <date>) &NBSP;
with integer value Returns the specified part of the date. This section is specified by datepart . &NBSP;
datepart  (dd, date)   equivalent to day  (date) &NBSP;
datepart  (mm, date)   equivalent to month  (date) &NBSP;
datepart  (yy, date)   equivalent to year  (date) &NBSP;

8, GETDATE () &NBSP;
to datetime  The default format returns the current date and time of the system  

http://blog.csdn.net/xiwu1616/article/details/14160889 turn

SQL string Functions