Summary of common functions of SQL Server

SQL de-whitespace function

1, LTrim (' content ')--Remove the space code to the left of the character as follows

DECLARE @str varcharset @str = '  adfadf ' select  @strselect ltrim (@str)

2, RTrim (' content ')---Remove the space to the right of the column value---function and ltrim (), no introduction here

3. LTrim (RTrim (' content ')): Remove the left and right spaces of the string

4. replace (calculated field, ', ')---Remove all the spaces in the computed field column values, as follows:

DECLARE @str varcharset @str = ' Hello work   ' select  @strselect REPLACE (@str, ', ')

SQL Common functions

1. Left ()---returns the character to the Ieft of the string: The code is as follows

DECLARE @str varcharset @str = ' hellowork ' select  @strselect left (@str, 2)

2. Right ()---returns the character to the left of the string

Use the opposite of the Left () method

3, substring (expression, start, length)--Returns the part of the string expression from the left of the first word character the length of a character.

DECLARE @str varcharset @str = ' hellowork ' select  SUBSTRING (@str, 6,4)

4, Len () (can also use datalength ())---Return the length of the string

DECLARE @str varcharset @str = ' hellowork ' select  datalength (@str) Select Len (@str)

5, Upper ()---to capitalize the string

DECLARE @str varcharset @str = ' hellowork ' select  Upper (@str)

6, lower ()---Convert the string to lowercase usage and the upper () function instead!

7, Stuff (character_expression, start, length, character_expression)--returns from the string Character_ The first character on the left of expression begins with the deletion of length characters, and the new string character_expression is inserted where start begins to delete.

DECLARE @str varcharset @str = ' hellowork ' select  STUFF (@str, 6,4, ' byby ')

8, CHARINDEX (expression1, expression2 [, Start_location])--expression1 is the character to be searched for Expression2, Start_location is Charind The EX function begins to find the position of expression1 in expression2, the CHARINDEX function returns an integer, and the returned integer is the position of the string to find in the string being searched. If CHARINDEX does not find the string to find, then the function integer "0"

DECLARE @str varcharset @str = ' hellowork ' select  CHARINDEX (' work ', @str) Select  CHARINDEX (' 4 ', @ Str

9, Patindex (%pattern%, expression)--function returns the starting position of a character or string in another string or expression, and the PATINDEX function supports the use of wildcards in the search string. This makes the PATINDEX function valuable for changing search strings.

DECLARE @str varcharset @str = ' hellowork ' select  patindex ('%work% ', @str) Select  patindex ('%4% ') , @str)

10. IsNull (' content ', '): Determine if the string is null, or return '

11, IsDate (' content '): Determine whether it is a time format, the words return 1, no words return 0

SQL Statistics Functions

AVG ()-return average value
Count ()-Number of rows returned
First ()-Returns the number one value
Last ()-Returns the final value
Max ()-The maximum value returned
MIN ()-Returns the minimum value
Total ()-The sum returned

SQL Date function SQL Server Date function

The following table lists the most important built-in date functions in SQL Server:

Function	Describe
GETDATE ()	Returns the current date and time
DATEPART (type,date)	Returns a separate part of the date/time
DATEADD (Type,number,date)	Add or subtract a specified time interval in a date
DateDiff (TYPE,DATE1,DATE2)	Returns a time between two dates
Convert ( data_type [(length)], expression [, style])	Display Date/time in a different format

--getdate get current Time Select GETDATE ()--dateadd original time add: 2013-02-17 13:20:16 this time plus 12 months select DATEADD (month,12, ' 2013-02-17 13:20 : + ')  --return: 2014-02-17 13:20:16.000  (parameter month can be changed to Day,year date plus corresponding value)--datediff two time difference (back-front = return value) Select DateDiff (Day, ' 2013-02-01 ', ' 2013-02-18 ')   --return:--------------------------the date  can be changed to Month,year and the corresponding value. Select DATEPART (Month, ' 2013-2-17 ')  --Returns 2    (parameter month can be changed to Day,year date plus corresponding value)--datename gets the string for the specified part select Datename (Weekday, ' 2013-2-17 ')  --Return Sunday (parameter weekday can be changed to day,year etc. date plus corresponding value)--day (), Month (), year () gets the string for the specified part select Day (' 2013-2-15 ')--return 15

Convert Date conversion function

Time format conversion using convert:convert (data_type [(length)], expression [, style])

Data_type
The data types provided by the target system, including bigint and sql_variant. You cannot use a user-defined data type.
Parameters
Length
Optional parameters for nchar, nvarchar, char, varchar, binary, or varbinary data types.

expression
is any valid microsoft®sql Server™ expression.
Style: Style

Select CONVERT (varchar), GETDATE (), 0): 2006 10:57amselect CONVERT (varchar), GETDATE (), 1): 05/16/06select C Onvert (varchar), GETDATE (), 2): 06.05.16Select convert (varchar), GETDATE (), 3): 16/05/06select CONVERT (varchar (+), GETDATE (), 4): 16.05.06Select convert (varchar), GETDATE (), 5): 16-05-06select convert (varchar), GETDATE ( ), 6): 06Select CONVERT (varchar (+), GETDATE (), 7): 06Select convert (varchar), GETDATE (8): 10:57:46se Lect CONVERT (varchar), GETDATE (), 9): 2006 10:57:46:827amselect CONVERT (varchar), GETDATE (), 05-16-06s Elect convert (varchar), GETDATE (), each): 06/05/16select convert (varchar), GETDATE (), 060516Select convert ( varchar (+), GETDATE (), (+): 2006 10:57:46:937select CONVERT (varchar), GETDATE (), (+): 10:57:46:967select CONV ERT (varchar), GETDATE (), 2006-05-16 10:57:47select CONVERT (varchar), GETDATE (), 21): 2006-05-16 10:57:47.157select CONVERT (varchar), GETDATE (): 05/16/06 10:57:47 amselect CONVERT (varchar), GETDATE (), max): 2006-05-16select CONVERT (varchar), GETDATE () : 10:57:47select CONVERT (varchar), GETDATE (), (+): 2006-05-16 10:57:47.250select convert (varchar), GETDATE (), (+): 2006 10:57amselect CONVERT (varchar (+), GETDATE (), 101): 05/16/2006select CONVERT (varchar), GETDATE ( ), 102): 2006.05.16Select CONVERT (varchar (104), GETDATE (), 103): 16/05/2006select convert (varchar), GETDATE (),) : 16.05.2006Select CONVERT (varchar), GETDATE (), max.: 16-05-2006select convert (varchar), GETDATE (), 106): 16 05 2 006Select CONVERT (varchar), GETDATE (), 107): 2006Select convert (varchar, GETDATE (), 108): 10:57:49select CONVERT (varchar), GETDATE (), 109): 2006 10:57:49:437amselect CONVERT (varchar), GETDATE (), 110): 05-16-2006select CONVERT (varchar), GETDATE (), 111): 2006/05/16select CONVERT (varchar), GETDATE (), 112): 20060516Select CONVERT (varchar), GETDATE (), 113): 162006 10:57:49:513select CONVERT (varchar), GETDATE (), (): 10:57:49:547select convert (varchar), GETDATE (), : 2006-05-16 10:57:49select CONVERT (varchar (+), GETDATE (), 121): 2006-05-16 10:57:49.700select convert (varchar ( (+), GETDATE (), 126): 2006-05-16t10:57:49.827select CONVERT (varchar), GETDATE (), ():???? ?????? 1427 10:57:49:907amselect CONVERT (varchar (+), GETDATE (), 131): 18/04/1427 10:57:49:920am

For more date function operations can be seen https://www.cnblogs.com/zhangpengnike/p/6122588.html

Summary of common functions of SQL Server