SQL Server built-in functions

Type conversion functions cast and convert

--cast is generally easier to use, and the advantage of convert is that you can format dates and valuesSelectCAST ('123'  as int)   --123SelectCONVERT (int,'123')  --123SelectCAST (123.4  as int)   --123SelectCONVERT (int,123.4)  --123 SelectCAST ('123.4'  as int) --failedSelectCONVERT (int,'123.4') --failedSelectCAST ('123.4'  as decimal)  --123SelectCONVERT (decimal,'123.4') --123 SelectCAST ('123.4'  as decimal(9,2))  --123.40SelectCONVERT (decimal(9,2),'123.4') --123.40Declare @Num moneySet@Num =1234.56SelectCONVERT (varchar ( -), @Num,0)  --1234.56SelectCONVERT (varchar ( -), @Num,1)  --1,234.56SelectCONVERT (varchar ( -), @Num,2)  --1234.5600

Character functions

--Len () is used to calculate the length of a stringSelectLEN ('123456') --6SelectLEN ('123') --3SelectLEN ('123') --3--Lower () is used to convert a string to lowercase, and upper () is used to convert a string to uppercaseSelectLower'ABC') --ABCSelectUpper'ABC') --ABC--LTrim () is used to remove a space to the left of a string, and RTrim () is used to remove the space to the right of a stringSelectLTrim'AAA') --AAASelectRTrim'AAA') --AAA--substring (string, Start_position,length) can take substrings of any length from any locationSelectSubstring'helloworld!',6,6)---world!--Left (string, length), starting from the left to take a substringSelectLeft'helloworld!',5)  --Hello--right (string, length), starting from the right to take a substringSelectRight'helloworld!',6)--world!--Replace (string, the string to be replaced, the string to replace)SelectReplace'aaabbbcccdddaaabbbcccddd','AA',' One') --11abbbcccddd11abbbcccddd--reverse (string_expression) returns the inverse value of a string valueSelectReverse'ABC') --CBA--deletes a specified length of character and inserts another set of characters at the specified starting point--Stuff (character_expression, start, length, character_expression)SelectStuff'aaabbbcccdddaaabbbcccddd',4,6,'222333')  --aaa222333dddaaabbbcccddd--repeats a string value at a specified number of times--replicate (String_Expression, integer_expression)SelectReplicate'123',5)  --123123123123123--returns the starting position of the specified expression in the string--charindex (expression1, expression2, start_location) or charindex (expression1, expression2)--expression1 start position in expression2SelectCharindex'H','Ellohworld') --5

SQL Server built-in functions