Summary of functions

--************************* string function **********************

--charindex searches for the position of the first character in the second character, returns a numeric value

Grammar:

CHARINDEX (' string 1', ' string 2')

Case: Get the position of "dream" in the string "Win in China, Dream and future"

Select CHARINDEX (' dream ', ' win in China, Dream and future ')

--len Gets the length of a string

Grammar:

Len (' string ')

Case: Get the length of the string "Win in China, Dream and future"

Select Len (' win in China, Dream and future ')

--upper converts a character in a string to uppercase in English, if there is a Chinese character in the string, does not give an error, does not perform the conversion

Syntax:Upper (' lowercase English characters ')

Case: Speaking of the string "I Hava a Dream" into uppercase

Select Upper (' I have a dream ! , I have a dream! ')

--ltrim clears the left space of the string and does not clear the right space

Grammar:

Ltrim (' string ')

Case: Clear string "Win in China, Dream and future" space on the Left

Select LTrim (' win in China, Dream and future ')

--rtrim Clears the space to the right of the string and does not clear the left space

Grammar:

Rtrim (' string ')

Case: Clear string "Win in China, Dream and future" space on the right

Select RTrim (' win in China, Dream and future ')

-- functions can be nested, such as clearing the left and right sides of the space

Grammar:

LTrim (RTrim (' string '))

Case: Clear string "Win in China, don't want to be with the future" space between the two sides

Select LTrim (RTrim (' win in China, Dream and future ')

--right Returns the specified number of characters from the right side of the string

Grammar:

Right (' string ', specifying the number of characters to search )

Case: Search for "future" from the right

Select Right (' win in China, Dream and future ', 2)

--replace replacing characters in a string

Grammar:

Replace (' string ', ' substituted character ', ' substituted character ')

Case study: Replace "and" with "and" in "win in China, Dream and future"

Select replace (' win in China, Dream and future ', ' and ', ' and ')

--stuff Deletes a character of a specified length in a string, substituting any other character for

Grammar:

Stuff (' string ', starting from the first few characters , Deleting a few characters ,' replacing the characters ')

Case: Delete win in China, change to make dream come true

Select Stuff (' win in China, Dream and future ', 1,4, ' Let dreams come true ')

--***************************** Date function *******************

--getdate Get the time of the system now

Grammar:

GETDATE ()

Case: Getting the current system time

Select GETDATE ()

--dateadd Add Time

Grammar:

Datedd ( year / month / day / time / minute / seconds , add how much, at what time period )

Case: Add two years on the basis of system time

Select DATEADD (Year,2,getdate ())

--datediff to seek the time difference, small times in front, big time in the rear

Grammar:

Datediff ( year / month / day / time / minute / seconds ,' little time ', ' big Time ')

Case: Calculate the number of hours from '1995-05-15' to now

Select DateDiff (Hour, ' 1995-05-15 ', GETDATE ())

--datename the string form of the specified date part, gets the day of the week

Grammar:

Datename (Dw,getdate ())

Case: Get today is the day of the week

Select datename (SS, GETDATE ())

--datepart Gets the integer of the specified date part

Syntax :

Datepart ( year / month / day , date )

Case: Get the month under system time now

Select datepart (mm, GETDATE ())

--***************************** mathematical function *******************

--rand returns The random number between 0-1

Grammar:

Rand ()

Case: Returning a random number between 0-1

Select rand ()

--abs The absolute value of an expression

Grammar:

ABS ( value )

Case: absolute value of 4

Select ABS (-4)

--ceiling Rounding up

Grammar:

Ceiling ( numeric )

Case: An integer that takes 49.001

Select Ceiling (49.001)

--floor Downward rounding

Grammar:

Floor ( value )

Case: An integer that takes 49.999

Select Floor (49.999)

--power The power value of an expression

Grammar:

Power ( number , number of times )

Case: Take 5 of the three-square value

Select Power(5,3)

--round Rounding

Grammar:

Round ( numeric , few after decimal )

Case: Rounding value of 49.501

Select Round (49.501,1)

--sign Integer returns 1, negative returns -1,0 returns 0

Grammar:

Sign ( value )

Case:- return value of 2

Select sign (-2)

--sqrt The square root of a value

Grammar:

SQRT ( numeric )

Case: Square root of 9

Select sqrt (9)

--***************************** system function *******************

--convert Conversion Data type

Grammar:

Convert ( data type , Another data type value )

Case: Converting a digital 12345 to a string

Select CONVERT (Nvarchap (50), 12345)

--***************************** aggregation function *******************

--sum () returns the sum of all values, only the number type

Grammar:

Sum ( column of numeric type )

Case: Calculating The sum of score columns

Select sum (Score)

From score

Where 1=1

--avg Returns the average of all values, only the number type

Grammar:

AVG(column of numeric type)

Case: Calculating The average of score columns

Select avg (Score)

From score

Where 1=1

--max() Maximum and min() minimum values, which can be numeric type, character type, date type

Grammar:

Max ( column name ) min(column name)

Case: Calculating The maximum value of a score column

Select Max (Score)

From score

Where 1=1

--count () count function,

Grammar:

Count(expression)

Case: Querying How many data are in the score table

Select Count (Score)

From score

Where 1=1

--1000 to the random number

Select Left (Rand () *8999+1000,4)

Summary of functions