Microsoft SQL Server Functions

Microsoft SQL Server Functions

Microsoft SQL Server Functions

In this post, I will explain about Microsoft SQL Server Functions, although this part is quite so long. So let’s start.

Microsoft SQL Server Functions

What is Microsoft SQL Server Functions built-in Function?

There are two types of functions in SQL Server are mentioned below –

  1. Predefined Functions
  2. User-defined Functions

Predefined Functions: Predefined functions return only one value like Boolean (0, 1), Character type, number, date and conversion (date to string, string to date, number to string and string to number)

User Defined Functions: User defined Functions return only one value like Boolean (0, 1), Character type, number, date and conversion (date to string, string to date, number to string and string to number)

Note: Above both functions are divided into two parts are mentioned below –

  1. Single Row Function
  2. Multiple Row Function.

Single Row Function: Single row function takes a single row as input and gives a single row output at a time. I.e. Char (), left () etc.

Multiple Row Function: Multiple row function takes multiple rows as input and gives a single row output at a time. I.e. max (), Min () etc.

SQL Server has many built-in functions: This reference contains string, numeric, date, conversion, and some advanced functions in SQL Server.

A – SQL Server String Functions:

ASCII (): Return the ASCII value of the first character in “CustomerName”

Image - 1

CHAR (): Returns the character based on the ASCII code.

Image - 2

CHARINDEX (): Returns the position of a substring in a string.

Image - 3

CONCAT (): Adds two or more strings together.

Image - 4

CONCAT WITH + (): Adds two or more strings together.

Image - 5

COL_LENGTH (): The COL_LENGTH () function returns the defined length of a column in a table.

Image - 6

DATALENGTH ():   Returns the number of bytes used to represent an expression.

Image - 7

DIFFERENCE (): Compares two SOUNDEX values, and returns an integer value.

Image - 8

LEFT ():   Extracts a number of characters from a string (starting from left).

Image - 9

LEN (): Returns the length of a string.

Image - 10

LOWER (): Converts a string to lower-case.

Image - 11

LTRIM (): Removes leading spaces from a string.

Image - 12

NCHAR (): Returns the Unicode character based on the number code.

Image - 13

PATINDEX (): Returns the position of a pattern in a string.

Image - 14

QUOTENAME ():   Returns a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier.

Image - 15

REPLACE (): Replaces all occurrences of a substring within a string, with a new substring.

Image - 16

REPLICATE (): Repeats a string a specified number of times.

Image - 18

REVERSE (): Reverses a string and returns the result.

Image - 19

RIGHT (): Extracts a number of characters from a string (starting from right).

Image - 20

RTRIM (): Removes trailing spaces from a string.

Image - 21

SOUNDEX (): Returns a four-character code to evaluate the similarity of two strings.

Image - 23

SPACE (): Returns a string of the specified number of space characters.

Image - 24

STR (): Returns a number as string.

Image - 25

STUFF (): Deletes a part of a string and then inserts another part into the string, starting at a specified position. It inserts the string at a given position, and deletes the number of characters specified from the original string.

Image - 26

SUBSTRING (): Extracts some characters from a string.

Image - 27

UNICODE (): Returns the Unicode value for the first character of the input expression.

Image - 28

UPPER ():  Converts a string to upper-case.

Image - 29

B – SQL Server Numerical Functions:

ABS (): Returns the absolute value of a number.

Image - 30

AVG (): Returns the average value of an expression.

Image - 31

CEILING (): Returns the smallest integer value that is >= a number.

Image - 32

COUNT (): Returns the number of records returned by a select query.

Image - 33

EXP (): Returns e raised to the power of a specified number.

Image - 34

FLOOR (): Returns the largest integer value that is <= to a number.

Image - 35

MAX ():  Returns the maximum value in a set of values.

Image - 36

MIN (): Returns the minimum value in a set of values.

Image - 37

PI ():  Returns the value of PI.

Image - 38

POWER (): Returns the value of a number raised to the power of another number.

Image - 39

RAND (): Returns a random number.

Image - 40

ROUND (): Rounds a number to a specified number of decimal places.

Image - 41

For Round Negative Logic:

                -1 —— >=5,   T= 5+5,     F= 5-5

                -2 —— >=50,  T= 50+50,   F=v50-50

                -3 —— >=500, T= 500+500, F= 500-500

SQRT ():  Returns the square of a number.

Image - 42

SQUARE (): Returns the square of a number.

Image - 43

SUM ():  Calculates the sum of a set of values.

Image - 44

C – SQL Server Date Functions:

CURRENT_TIMESTAMP ():  Returns the current date and time.

Image - 45

DATEADD (): Adds a time/date interval to a date and then returns the date.

Syntaxt: DATEADD(intervalnumberdate)

Image - 46

Parameter Values:

Image - 47

DATEDIFF (): Returns the difference between two dates.

Syntax: DATEDIFF(intervaldate1date2)

Image - 48

DATEFROMPARTS (): Returns a date from the specified parts (year, month, and day values).

Syntax: DATEFROMPARTS (yearmonthday)

Image - 49

DATENAME (): Returns a specified part of a date (as string).

Image - 50

DATEPART (): Returns a specified part of a date (as integer).

Image - 51
Image - 52
Image - 53

DAY (): Returns the day of the month for a specified date.

Image - 54

GETDATE (): Returns the current database system date and time.

Image - 55

GETUTCDATE (): Returns the current database system UTC date and time.

Image - 56

ISDATE (): Checks an expression and returns 1 if it is a valid date, otherwise 0.

Image - 57

MONTH (): Returns the month part for a specified date (a number from 1 to 12).

Image - 58

SYSDATETIME (): Returns the date and time of the SQL Server.

Image - 59

YEAR ():  Returns the year part for a specified date.

Image - 60

D – SQL Server Advanced Functions:

CAST ():  Converts a value (of any type) into a specified data type.

Image - 61

COALESCE (): Returns the first non-null value in a list.

Image - 62

CONVERT ():  Converts a value (of any type) into a specified datatype. The CONVERT() function is used to convert columns from one data type to another data type. One typical use of this convert() function that is used to specify the required format of a date field when converting it to CHAR or VARCHAR from DATETIME.

Syntax: CONVERT (data_type(length), expression, style)

Image - 63

CURRENT_USER (): Returns the name of the current user in the SQL Server database.

Image - 64

ISNULL (): Return a specified value if the expression is NULL, otherwise return the expression.

Syntax: ISNULL (expressionvalue)

Image - 65

ISNUMERIC (): Tests whether an expression is numeric.

Image - 66

NULLIF (): Returns NULL if two expressions are equal.

Syntax: NULLIF(expr1expr2)

Image - 67

SESSION_USER (): Returns the name of the current user in the SQL Server database.

Image - 68

SESSIONPROPERTY (): Returns the session settings for a specified option.

Image - 69

SYSTEM_USER (): Returns the login name for the current user.

Image - 70

USER_NAME (): Returns the database user name based on the specified id.

Syntax:   USER_NAME(id_number)

Image - 71

USER_ID ():  The USER_ID() function takes a name string and returns the current database ID for that name.

 Syntax: USER_ID ( [ ‘user’ ] ) 

Image - 72

E – SQL Server Pivot and Un-Pivot Functions:

Pivot and Un-pivot are relational operators which can be used to change a table valued expression into another table.

Syntax:

Image - 73

PIVOT FUNCTION: Pivot takes rows and puts them into column.

Syntax:

Image - 74

UN-PIVOT FUNCTION: Un-pivot takes columns and puts them into rows.

Syntax:

Image - 75

F – SQL Server Ranking Functions:

  1. RANK ():  Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

Syntax-

Image - 76

2. DENSE_RANK():  Dense_rank returns the rank of a row in the partition of a result-set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

Syntax-

Image - 77

3. ROW_NUMBER(): Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Syntax-

Image - 78

4. NTILE(): Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

Syntax-

Image - 79

Example:

Image - 80

We will discuss upcoming topics in next part. So please take care of you.

Assuming this tutorial post has well-acquainted aspiring to the complete requirement. If you want to download this tutorial in PDF format file, then please – click here to download PDF.

Also requesting, if you have any ideas or suggestions for me or you need some important blogs/Videos or guidelines related to SQL Server, Excel, WordPress, SEO, Website technologies then please feel free to write me on below comment section or you can go through the Contact Page. I will definitely come back to you with your desired requirements.

Hope this tutorial post was helpful for you and you liked very well, I am happy to serve you better. If you liked really this tutorial post, please don’t forget to share on social media like – Facebook, Whatsapp, LinkedIn, Twitter, Instagram, etc.

Leave a Reply