Common SQL Server Functions and date operations

The following information is collected from the http://hi.baidu.com/delphistu,http://hi.baidu.com/zhvsby

The description may be incorrect. Please verify yourself

 

SQL Server common function set

I. character conversion functions
1. ASCII ()
Returns the ASCII value of the leftmost character of a character expression. In the ASCII () function, strings with numbers are not enclosed by '', But strings with other characters must be enclosed by''. Otherwise, an error occurs.
2. Char ()
Converts an ascii code to a character. If no value is entered ~ ASCII code value between 255, char () returns NULL.
3. Lower () and Upper ()
Lower () converts all strings to lowercase letters, and upper () converts all strings to uppercase letters.
4. STR ()
Converts numeric data to numeric data.
STR (<float_expression> [, length [, <decimal>])
Length indicates the length of the returned string, and decimal indicates the number of decimal places returned. If no length is specified, the default length value is 10 and the default value of decimal is 0.
If length or decimal is a negative value, null is returned;
When length is less than the number of digits to the left of the decimal point (including the symbol bit), return length *;
Obey length first, and then take decimal;
When the number of digits of the returned string is less than length, spaces are filled on the left.
Ii. Space-removing Functions
1. ltrim () removes spaces in the string header.

2. rtrim () removes spaces at the end of the string.

Iii. substring Functions
1. Left ()
Left (<character_expression>, <integer_expression>)
Returns character_expression, which starts from integer_expression at the left.

2. Right ()
Right (<character_expression>, <integer_expression>)
Returns character_expression, which is a string of characters starting from integer_expression.

3. substring ()
Substring (<expression>, <starting _ Position>, length)
Returns the length of the string starting from the start _ Position Character on the left.

Iv. String comparison functions
1. charindex ()
Returns the starting position of a specified substring in a string.
Charindex (<'substring _ expression' >,< expression>)
Here, substring _ expression is the character expression to be searched, and expression can be a string or a column name expression. If no substring is found, the return value is 0.
This function cannot be used for text and image data types.

2. patindex ()
Returns the starting position of a specified substring in a string.
Patindex (<'% substring _ expression %'>, <column _ name>) must have a percent sign (%) before and after the substring expression; otherwise, the return value is 0.
Unlike the charindex function, wildcards can be used in the child strings of the patindex function, which can be used for char, varchar, and text data types.

V. String operation functions
1. quotename ()
Returns a string enclosed by a specific character.
Quotename (<'character _ expression'> [, quote _ character]). Quote _ character indicates the character used to enclose the string. The default value is "[]".

2. Replicate ()
Returns a character string that repeats character_expression for a specified number of times.
Replicate (character_expression integer_expression) if the value of integer_expression is negative, null is returned.

3. Reverse ()
Sorts the characters of a specified string in reverse order.
Reverse (<character_expression>) Where character_expression can be a string, a constant, or a column value.

4. Replace ()
Returns the string with the specified substring replaced.
Replace (<string_expression1>, <string_expression2>, <string_expression3>) Use string_expression3 to replace string_expression2 in string_expression1.

4. Space ()
Returns a blank string with a specified length.
Space (<integer_expression>) if the value of integer_expression is negative, null is returned.

5. Stuff ()
Replace the specified position and length of the string with another substring.
Stuff (<character_expression1>, <start _ Position>, <length>, <character_expression2>)
If the start position is negative or the length value is negative, or the start position is greater than the length of character_expression1, the return value is null.
If the length is greater than the right length of start _ Position in character_expression1, character_expression1 retains only the first character.
Vi. Data type conversion functions
1. Cast ()
Cast (<expression> as <data _ type> [length])

2. Convert ()
Convert (<data _ type> [length], <expression> [, style])

1) data_type isSQL ServerThe data type defined by the system. The user-defined data type cannot be used here.
2) length is used to specify the data length. The default value is 30.
3) convert the char or varchar type to an integer type such as Int or samllint. The result must be a value with a positive or negative number.
4) The conversion from text to Char or varchar is a maximum of 8000 characters, that is, the char or varchar data type is of the maximum length.
5) data stored in the image type is converted to the binary or varbinary type, up to 8000 characters.
6) convert the integer to the money or smallmoney type and process it according to the currency unit of the defined country, such as RMB, USD, and pound.
7) the bit type conversion converts a non-zero value to 1 and is still stored as a bit.
8) if you try to convert the data type to different lengths, the converted value will be truncated and "+" will be displayed after the converted value to identify the occurrence of this truncation.
9) use the style. Option of the convert () function to display the date and time in different formats. Style is the conversion style number provided by the SQL server system when converting datatime and smalldatetime to a string. Different style numbers have different output formats.
VII. Date Functions
1. Day (date_expression)
Returns the date value in date_expression.

2. Month (date_expression)
Returns the month value in date_expression.

3. Year (date_expression)
Returns the Year Value in date_expression.

4. dateadd ()
Dateadd (<datepart>, <number>, <date>)
Returns the new date generated by the specified date plus the specified additional date interval number.

5. datediff ()
Datediff (<datepart>, <date1>, <date2>)
Returns the difference between two specified dates in datepart, that is, the difference between date2 and date1. The result value is an integer with positive and negative signs.

6. datename ()
Datename (<datepart>, <date>)
Returns the specified part of the date in the form of a string. Specified by datepart.

7. datepart ()
Datepart (<datepart>, <date>)
Returns the specified part of the date in the form of an integer. This part is specified by datepart.
Datepart (DD, date) is equivalent to day (date)
Datepart (mm, date) is equivalent to month (date)
Datepart (YY, date) is equivalent to year (date)

8. getdate ()
Returns the current date and time of the system in the default format of datetime.

 

Example:

 

1. 1. substring, reverse
2. In the user's phone number, the third digit is 2, the second digit is 6, and the last digit is an even number.
3. Create Table tab1 (Tel varchar (50 ))
4. Select * From tab1
5. Insert into tab1 values ('20140901 ')
6. Insert tab1 values ('20140901 ')
7. Insert tab1 values ('20140901 ')
8. Insert tab1 values ('20140901 ')
9. Insert tab1 values ('20140901 ')
11. Select * From tab1
12. Select * From tab1 where substring (reverse (TEL), 3, 1) = 2
13. Select * From tab1 where substring (reverse (TEL), 3, 1) = 2 and substring (reverse (TEL), 2, 1) = 6
15. Select * From tab1 where substring (reverse (TEL), 3, 1) = 2 and substring (reverse (TEL), 2, 1) = 6
16. And substring (reverse (TEL), 1, 1) % 2 = 0
20. 2. Replicate
22. Select replicate ('abc', 2)
23. Result: abcabc
25. Select replicate ('abc',-2)
26. Result: null.
28. Select replicate ('abc', 0)
29. Result: none (empty string)
31. 3. quotename
33. Select quotename ('abc [] def ','{')
34. Result: {ABC [] def}
36. Select quotename ('abc [] def ')
37. Result: [ABC [] def]
39. []
41. 4. Stuff, space
42. There are three fields in the database table, which must be output in the format of XXXX-xxxxxx. If the number is less than the corresponding number of digits, fill in spaces
43. Output the numbers like 879 and to the report in the following format: 232.16-1234316-879 ___. The color of the underline represents a space.
45. Stuff (space (4), 1, Len ('23'), '23') + '-' + stuff (space (6), 1, len ('123'), '123') + '-' + stuff (space (6), 1, Len ('123'), '123 ')
47. 5. Str
48. Print STR (12, 10)
49. Result: 12
50. Print STR (1223, 2)
51. Result :**
52. Print STR (123.46)
53. Result: ### 123.5 where # represents a space. The total length of the result is 8. Take one decimal place and the decimal place is rounded to five. If the SQL statement takes three decimal places, perform the zero-padding operation.
55. 6. charindex
56. Select charindex ('ea ', 'abcdeabcd ')
58. Result: 5
60. 7. patindex
61. Example 1:
62. Find out that the description field in the northwind. DBO. Categories table contains all records containing the word "Bread" or "Bread". Then the selection statement may be like this:
64. Select description from northwind. DBO. Categories
65. Where patindex ('% [B, B] Read %', description)> 0
66. In patindex, wildcards can be used to determine the upper and lower case "B"
68. Example 2:
69. Find out that the description field in the northwind. DBO. Categories table contains the word "Bread" or "Bread", and the second sub-letter is not the record of "E.
71. Select description from northwind. DBO. Categories
72. Where patindex ('% [B, B] Read %', description)> 0 and patindex ('_ [^ e] %', description) = 1
73. By adding a patindex function using the ^ wildcard in the Condition Statement, We can filter out the "dessert, candies, and sweet breads" record. The preceding query results have only one record.

SQL Server Date and Time formatting

Select convert (varchar (100), getdate (), 0): 05 16 2006 AM
Select convert (varchar (100), getdate (), 1): 05/16/06
Select convert (varchar (100), getdate (), 2): 06.05.16
Select convert (varchar (100), getdate (), 3): 16/05/06
Select convert (varchar (100), getdate (), 4): 16.05.06
Select convert (varchar (100), getdate (), 5): 16-05-06
Select convert (varchar (100), getdate (), 6): 16 05 06
Select convert (varchar (100), getdate (), 7): 05 16, 06
Select convert (varchar (100), getdate (), 8): 10:57:46
Select convert (varchar (100), getdate (), 9): 05 16 2006 10: 57: 46: 827am
Select convert (varchar (100), getdate (), 10): 05-16-06
Select convert (varchar (100), getdate (), 11): 06/05/16
Select convert (varchar (100), getdate (), 12): 060516
Select convert (varchar (100), getdate (), 13): 16 05 2006 10: 57: 46: 937
Select convert (varchar (100), getdate (), 14): 10: 57: 46: 967
Select convert (varchar (100), getdate (), 20): 10:57:47
Select convert (varchar (100), getdate (), 21): 10:57:47. 157
Select convert (varchar (100), getdate (), 22): 05/16/06 10:57:47 AM
Select convert (varchar (100), getdate (), 23 ):
Select convert (varchar (100), getdate (), 24): 10:57:47
Select convert (varchar (100), getdate (), 25): 10:57:47. 250
Select convert (varchar (100), getdate (), 100): 05 16 2006 AM
Select convert (varchar (100), getdate (), 101): 05/16/2006
Select convert (varchar (100), getdate (), 102): 2006.05.16
Select convert (varchar (100), getdate (), 103): 16/05/2006
Select convert (varchar (100), getdate (), 104): 16.05.2006
Select convert (varchar (100), getdate (), 105): 16-05-2006
Select convert (varchar (100), getdate (), 106): 16 05 2006
Select convert (varchar (100), getdate (), 107): 05 16,200 6
Select convert (varchar (100), getdate (), 108): 10:57:49
Select convert (varchar (100), getdate (), 109): 05 16 2006 10: 57: 49: 437am
Select convert (varchar (100), getdate (), 110): 05-16-2006
Select convert (varchar (100), getdate (), 111): 2006/05/16
Select convert (varchar (100), getdate (), 112): 20060516
Select convert (varchar (100), getdate (), 113): 16 05 2006 10: 57: 49: 513
Select convert (varchar (100), getdate (), 114): 10: 57: 49: 547
Select convert (varchar (100), getdate (), 120): 10:57:49
Select convert (varchar (100), getdate (), 121): 10:57:49. 700
Select convert (varchar (100), getdate (), 126): 2006-05-16t10: 57: 49.827
Select convert (varchar (100), getdate (), 130): 18 ???? ?????? 1427 10: 57: 49: 907am
Select convert (varchar (100), getdate (), 131): 18/04/1427 10: 57: 49: 920am

Frequently used:
Select convert (varchar (100), getdate (), 8): 10:57:46
Select convert (varchar (100), getdate (), 24): 10:57:47
Select convert (varchar (100), getdate (), 108): 10:57:49
Select convert (varchar (100), getdate (), 12): 060516
Select convert (varchar (100), getdate (), 23 ):

 

SQL Server date operation set

Declare @ DT datetime
Set @ dt = getdate ()

-- 1. Short Date Format: yyyy-m-d
Select Replace (convert (varchar (10), @ DT, 120), N'-0 ','-')

-- 2. Long Date Format: yyyy-mm-dd
-- A. method 1
Select stuff (convert (char (8), @ DT, 112), 5, 0, N 'Year'), 8, 0, N 'month') + N'
-- B. method 2
Select datename (year, @ DT) + n'year' + datename (month, @ DT) + N' + datename (day, @ DT) + N'

-- 3. Long Date Format: yyyy-mm-dd
Select datename (year, @ DT) + N 'Year' + Cast (datepart (month, @ DT) as varchar) + N 'month' + datename (day, @ DT) + n'day'

-- 4. Complete date + time format: yyyy-mm-dd hh: MI: SS: Mmm
Select convert (char (11), @ DT, 120) + convert (char (12), @ DT, 114)

2. Date Calculation

Declare @ DT datetime
Set @ dt = getdate ()

Declare @ number int
Set @ number = 3

-- 1. Specify the first or last day of the year
-- A. The first day of the year
Select convert (char (5), @ DT, 120) + '1-1'

-- B. The last day of the year
Select convert (char (5), @ DT, 120) + '12-31'

-- 2. Specify the first or last day of the quarter where the date is located.
-- A. The first day of the quarter
Select convert (datetime,
Convert (char (8 ),
Dateadd (month,
Datepart (quarter, @ DT) * 3-month (@ DT)-2,
@ DT ),
120) + '1 ')

-- B. The last day of the quarter)
Select convert (datetime,
Convert (char (8 ),
Dateadd (month,
Datepart (quarter, @ DT) * 3-month (@ DT ),
@ DT ),
120)
+ Case when datepart (quarter, @ DT) in (1, 4)
Then '31 'else' 30' end)

-- C. The last day of the quarter (Direct Algorithm)
Select dateadd (day,-1,
Convert (char (8 ),
Dateadd (month,
1 + datepart (quarter, @ DT) * 3-month (@ DT ),
@ DT ),
120) + '1 ')

-- 3. Specify the first or last day of the month in which the date is located.
-- A. The first day of the month
Select convert (datetime, convert (char (8), @ DT, 120) + '1 ')

-- B. The last day of the month
Select dateadd (day,-1, convert (char (8), dateadd (month, 1, @ DT), 120) + '1 ')

-- C. The last day of the month (this method is incorrect. Only the last day of February can be obtained for correct solution)
Select dateadd (month, 1, dateadd (day,-day (@ DT), @ DT ))

-- 4. Specify any day of the week of the date
Select dateadd (day, @ number-datepart (weekday, @ DT), @ DT)

-- 5. Specify any day of the week of the date
-- A. Sunday is the 1st day of a week.
Select dateadd (day, @ number-(datepart (weekday, @ DT) + @ DATEFIRST-1) % 7, @ DT)

-- B. Monday is the 1st day of a week.
Select dateadd (day, @ number-(datepart (weekday, @ DT) + @ DATEFIRST-2) % 7-1, @ DT)