Shulou(Shulou.com)06/01 Report--

I. String functions

datalength (Char_expr) Returns the number of characters in a string, but does not include spaces

substring (expression, start, length)

right (char_expr, int_expr) Returns int_expr characters on the right side of the string

left(， )

Returns character_expression integer_expression characters from the left.

ltrim () removes spaces from the head of a string.

rtrim () removes spaces from the end of a string.

str ([, length [,]]) converts numeric data to character data

length specifies the length of the returned string and decimal specifies the number of decimal places to return. If no length is specified, the default value for length is 10, and the default value for decimal 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 digit), return length *;

Obey length first, then decimal;

When the number of digits in the returned string is less than length, the left side is complemented by spaces.

upper (char_expr) to upper case

lower (char_expr) to lowercase

space (int_expr) generates int_expr spaces

duplicate (char_expr, int_expr) Duplicate string int_expr times

reverse (char_expr) Reverse string

stuff (char_expr1,start,length,char_expr2)

The length characters starting with start are replaced by char_expr2

ltrim (char_expr) rtrim (char_expr) remove spaces

ascii (char) char (ascii) Two functions correspond, take ascii code, take characters according to ascii code

char () converts ASCII code to characters. If no ASCII value between 0 and 255 is entered, CHAR () returns NULL.

charindex (char_expr, expression) Returns the starting position of char_expr

patindex (" % pattern %", expression) Returns the starting position of the specified pattern, otherwise 0

II. Mathematical functions

trunc (45.923, 1) truncates decimal numbers to specified precision Result: 45.9 This is an oracle function

mod (1600,300) Find the remainder of the division: 100

abs (numeric_expr) for absolute values

ceiling (numeric_expr) takes the smallest integer greater than or equal to the specified value

avg (numeric_expr) Average

exp (float_expr) takes exponent

floor (numeric_expr) is less than or equal to the maximum integer of the specified value

pi() 3.1415926.........

power (numeric_expr, power) Returns power to the power

rand ([int_expr]) Random number generator

round (numeric_expr, int_expr) int_expr specified precision rounded

sign (int_expr) Returns +1,0,-1 based on positive, 0, negative,

sqrt (float_expr) square root

3. Date Time Function

getdate () returns the date

getutcdate ()--Get utc time

day (getdate ())--remove day

month (getdate ())--remove month

year (getdate ())--retrieve year

datename (datepart, date_expr) Return name e.g. June

datepart (datepart, date_expr) takes part of the date

datediff (datepart,date_expr1.dateexpr2) Date difference

dateadd (datepart, number, date_expr) Returns the date plus the number

The value and meaning of datepart in the above function:

yy 1753 - 9999 years

qq 1 - 4 hours

mm 1-December

1 - 366 days

dd 1 - 31

wk 1 - 54 weeks

dw 1 - 7 weeks

hh 0 - 23 hours

mi 0 - 59 minutes

ss 0 - 59 seconds

ms 0 - 999 ms

IV. Other functions of the system

suser_name () User login name

user_name () The name of the user in the database

user's name in the database

show_role () Rules that work for the current user

db_name () Database name

object_name (obj_id) Database object name

col_name (obj_id, col_id) Column name

col_length (objname, colname) Column length

valid_name (char_expr) is a valid identifier

V. Type conversion function

convert (data type [(length)], expression [, style])

Explicitly convert an expression of one data type to an expression of another data type;

Length: If the data type allows setting the length, you can set the length, such as varchar (10);

Style: A style used to convert date type data to date format of character data type.

Style IDStyle format 100 or 0mon dd yy hh: miAM (or PM) 101mm/dd/yy102yy.mm.dd103dd/mm/yy104dd. mm. yy105dd-mm-yy106dd yymon 107Mon dd, yy108hh: mm: ss109 or 9mon dd yhh: mi: ss: mmmAM (or PM) 110mm-dd-yy111yy/mm/dd112yymmdd113 or 13dd mon yy hh: mm: ss: mmm (24h) 114hh: mi: ss: mmm (24h) 120 or 20y-mm-dd hh: mi: ss (24h) 121 or 21y-mm-dd hh: mi: ss. mmm (24h) 126y-mm-ddThh: mm: ss. mmm (no blank) 130dd mon yy hh: mi: ss: mmmAM131dd/mm/yy hh: mi: ss: mmmAM

For example:

CONVERT (VARCHAR (19), GETDATE ()) Returns: Dec 29 2008 11:45 PM

CONVERT (VARCHAR (10), GETDATE (),110) Returns: 12 - 29 - 2008

CONVERT (VARCHAR (11), GETDATE (),106) Returns: 29 Dec 08

CONVERT (VARCHAR (24), GETDATE (),113) Returns: 29 Dec 2008 16:25: 46.635

cast (expression AS data type [(length)])

Explicitly converts an expression of one data type to an expression of another data type.

For example: select cast (123 as nvarchar) returns 123

select N 'Age: '+ cast (23 as nvarchar) Return Age: 23

VI. System function

newid () No arguments

Returns a GUID (Globally Unique Identifier) value

Select newid ()

Return: 2 E6861 EF-F4 DB-4FFE-86 EB-637482 FE 982 J2

isnumeric (arbitrary expression)

Determines whether the expression is of numeric type or can be converted to a numeric value.

Yes: returns 1, No: returns 0

For example: select isnumeric (1111) returns 1

select isnumeric ('123 rr') returns 0

select isnumeric ('123 ') returns 1

isnull (arbitrary expression 1, arbitrary expression 2)

Returns the value of any Expression 1 if it is not NULL; otherwise, returns the value of any Expression 2 after converting the type of any Expression 2 to the type of any Expression 1 if the two types are different.

For example: select isnull (null, N 'no value') returns no value

select isnull (N 'specific value', N' no value') Returns a specific value

isdate (arbitrary expression)

Determines whether the input expression is a valid date or a date that can be converted to a valid date;

Yes: returns 1, No: returns 0

For example: select isdate (getdate ()) returns 1

selectisdate ('2013 - 01 - 02') returns 1

selectisdate ('198 ') returns 0

VII. Ranking function

row_number No arguments

Numbering each row in the result set, starting with 1 and increasing by 1, often used to generate serial numbers;

Example: select row_number () over (order by userid desc) as [NO], username, password from T_USER

VIII. Aggregation function

count()

Returns the total number of entries in the group, count (*) returns all entries in the group, including NULL values and duplicate value items, if the expression is written, the null value is ignored, and the expression is an arbitrary expression.

max()

Returns the maximum value in a group, null values are ignored, expressions are numeric expressions, string expressions, dates.

min()

Returns the smallest value in a group, null values are ignored, expressions are numeric expressions, string expressions, dates.

sum()

Returns the sum of all values in a group, null values are ignored, and expressions are data expressions.

avg()

Returns the average of all values in a group, null values are ignored, and expressions are data expressions.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.