You are here
Home > Sql Server > SQL Server Functions in sql

SQL Server Functions in sql

SQL Server Functions

  • T-SQL supports many built-in functions.
  • Scalar function returns a single value.
  • Table-valued function returns a table result.

 Conversion functions

  • SQL supports number of functions that can convert a source expression to a target data type.
  • SQL Server conversion functions are CAST and CONVERT.

CAST:

  • It converts an expression from one data type to another data type.
  • Syntax: CAST(sourceexpression AS targettype)
  • Example: select CAST(’88’ AS INT);

CONVERT:

  • It is handy when you need to specify a style for the conversion.
  • Syntax: CONVERT(targettype, sourceexpression [, stylenumber])
  • Example: select CONVERT(DATE, ’01/02/2016′,101);

 

Date and time functions

  • T-SQL supports a many date and time functions that allow you to manipulate your date and time data.

CURRENT_TIMESTAMP:

  • Returns the current date and time.

SYSDATETIME:

  • It returns a datetime(2) value that contains the data and time of the computer on which the SQL is running.
  • Example: select SYSDATETIME();

SYSUTCDATETIME:

  • It returns a datetime value that contains the data and time of the computer on which the SQL is running and the date and time is returned as UTC time.
  • Example: select SYSUTCDATETIME();

GETUTCDATETIME:

  • It returns the current date and time as UTC time.

SYSDATETIMEOFFSET:

  • It returns a datetimeoffset value that contains the date and time of the computer on which sql running. The timezone offset is included.
  • Example: select SYSDATETIMEOFFSET();

DATEFROMPARTS:

  • It returns a date value for the specified year, month and year, day.
  • Example: select DATEFROMPARTS(2016,02,14);

DATEADD:

  • Using this function, you can add a requested number of units of a specified part to a specified date and time value.
  • Example: select DATEADD(year,2, ‘20160214’);

DATEDIFF:

  • It returns the difference between two date values based on the interval specified.
  • Example: select DATEDIFF(day, ‘20160214’, ‘20170214’);

DATENAME:

  • It returns a specified part of a given date as a string value.

DATEPART:

  • It returns a specified part of a given date as an integer value.
  • Example: select DATEPART(month, ‘20160214’);

DAY:

  • It returns the day of the month for a given date.

MONTH:

  • It returns the month for a given date.
  • Example: select MONTH(‘20160214’);

YEAR:

  • It returns the year for a given date.

GETDATE:

  • It returns a current date and time.

 

String Functions

  • T-SQL supports a many string functions like LEN, DATALENGTH, CHARINDEX, PATINDEX, LTRIM, RTRIM, REPLACE, STUFF etc .

CONCAT:

  • It concatenates two or more strings together.

CHAR:

  • It returns the ASCII character based on the number code.

CHARINDEX:

  • It returns the location of a substring in a string.

PATINDEX:

  • It returns the location of a pattern in a string.
  • Example: select PATINDEX(‘%[0-9]% ‘, ‘abc12abc’);

DATALENGTH:

  • It returns the length of an expression.
  • Example: select DATALENGTH(‘abc’);

LEN:

  • It returns the length of the specified string.
  • Example: select LEN(‘abc’);

SUBSTRING:

  • It extracts a substring from a string.
  • Example: select SUBSTRING(‘abcdef’,1,4);

LEFT:

  • It extracts a substring from a string starting from left.
  • Example: select LEFT(‘abcdef’,4);

RIGHT:

  • It extracts a substring from a string starting from right.
  • Example: select RIGHT(‘abcdef’,2);

LTRIM:

  • It removes leading spaces from a string.
  • Example: select LTRIM(‘ abcd’);

RTRIM:

  • It removes trailing spaces from a string.
  • Example: select RTRIM(‘abcd ‘);

REPLACE:

  • It replaces all occurrences of a specified string value with another string value.
  • Example: select REPLACE(‘.4.5.6’, ‘. ‘,/ ‘);

STUFF:

  • It deletes a sequence of characters from a string and then inserts another sequence of characters into the string, starting at a specified position.

LOWER:

  • It converts a string in to lower case.

UPPER:

  • It converts a string in to upper case.

 

Numeric Functions

  • T-SQL supports a many numeric functions like MAX, MIN, AVG, SUM, ABS, FLOOR, SQRT, RAND etc.

COUNT:

  • It returns the number of rows in the table, including duplicates.

SUM:

  • It returns the sum of all the values or only the distinct value in the expression.

AVG:

  • It returns the average of the values in a group.

MAX:

  • It returns the maximum value in the expression.

MIN:

  • It returns the minimum value of an expression.

SQRT:

  • It returns the square root of the specified value.

RAND:

  • It returns a random number within a range.

ABS:

  • It returns an absolute value of the number.

CEILING:

  • It returns the smallest integer value that is greater than or equal to a number.

FLOOR:

  • It returns the largest integer value that is less than or equal to a number.

ROUND:

  • It returns a number rounded to a certain number of decimal places.

 

Advanced Functions

  • T-SQL supports a many advanced functions like COALESCE, ISNULL, NULLIF, ISDATE, ISNUMERIC, CURRENT USER, SESSION USER, SYSTEM USER etc.

COALESCE:

  • It returns the first non-null value among its parameter.
  • Syntax: COALESCE (column1, column2, …., columnN)

ISNULL:

  • It returns first value that is not null among their input.
  • Syntax: ISNULL (columnname, replacement_value)

NULLIF:

  • It compares two expressions.
  • Syntax: NULLIF (expression1, expression2)

ISDATE:

  • It returns 1 if the expression is a valid date otherwise it returns 0.

ISNUMERIC:

  • It returns 1 if the expression is a valid number otherwise it returns 0.

CURRENT USER:

  • It returns name of the current user in the SQL Server database.

SESSION USER:

  • It returns user name of the current session in the SQL Server database.

SYSTEM USER:

  • It returns login name information for the current user in the SQL Server database.

USER NAME:

  • It returns the user name in the SQL Server database.

SESSIONPROPERTY:

  • It returns the setting for a specified option of a session.

 

 

Keep visiting my site ask2tech.com for upcoming top technical article

Learn more about DBA at TechNet Consultancy

Leave a Reply

Top