SQL Server Basic functions

Source: Internet
Author: User
Tags date expression functions integer mathematical functions sql return square root
server| function 1. String function
Length and analytical use

DATALENGTH (char_expr) returns the string contains the number of characters, but does not contain the trailing spaces

SUBSTRING (expression,start,length) Don't say much, take the substring

Right (char_expr,int_expr) returns the int_expr character on the right-hand side of the string

Character Manipulation class

Upper (char_expr) to uppercase

Lower (char_expr) to lowercase

Space (int_expr) generates int_expr spaces

Replicate (char_expr,int_expr) copy string int_expr times

Reverse (char_expr) inverted string

Stuff (CHAR_EXPR1,START,LENGTH,CHAR_EXPR2) The string char_expr1 from the

The length character of start begins with CHAR_EXPR2 instead

LTrim (char_expr) RTrim (char_expr) Remove space


ASCII (char) char (ASCII) two functions, taking ASCII code, taking characters according to ASCII


String Lookup

CHARINDEX (char_expr,expression) returns the starting position of char_expr

Patindex ("%pattern%", expression) returns the starting position of the specified pattern, otherwise 0


2. Mathematical functions

ABS (NUMERIC_EXPR) to find absolute value

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

EXP (float_expr) index

Floor (numeric_expr) is less than or equal to the maximum integer worth specified

Pi () 3.1415926 .....

Power (Numeric_expr,power) returns power to the second party

RAND ([int_expr]) random number generator

Round (numeric_expr,int_expr) Ann int_expr specified precision rounding

Sign (INT_EXPR), based on positive numbers, 0, negative numbers, returns +1,0,-1

sqrt (float_expr) square root


3. Date function

GETDATE () Return date

Datename (datepart,date_expr) return name such as June

DATEPART (datepart,date_expr) take the date part

DateDiff (DATEPART,DATE_EXPR1.DATEEXPR2) Date Difference

DATEADD (datepart,number,date_expr) return date plus number

The datepart in the above function

The value and significance of the formulation

YY 1753-9,999 part

QQ 1-4 Moment

MM January-December

Dy 1-366 Days

DD 1-31 Days

WK 1-54 weeks

DW 1-7 weeks

HH 0-23 Hours

Mi 0-59 minutes

SS 0-59 sec

Ms 0-999 ms


Date conversion

Convert ()


4. System function

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




Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.