SQL SERVER Common functions

1 , SUBSTRING

function: returns part of a character, binary, text, or image expression

Syntax: SUBSTRING (expression, start, length)

The substring function in SQL is used to grab a portion of a field data. The name of this function is not exactly the same in different repositories:

Oracle:substr () SQL server:substring ()

Parameters:

An expression string, binary string, text, image, column, or expressions that contain columns. Do not use an expression that contains an aggregate function.

Start integer or an expression that can be implicitly converted to int, specifying the starting position of the substring.

A length integer or an expression that can be implicitly converted to an int, specifying the substring's lengths.

return value:

If expression is a supported character data type, character data is returned. If expression is a supported binary data type, then binary data is returned. If start = 1, the substring starts with the first character of the expression.

2 , UPPER and the LOWER

3 , CONVERT and the CAST

Explicitly converts an expression of a data type to another data type. CAST and CONVERT provide similar functionality.

CAST Syntax:

CAST (expression as data_type [(length)])
CONVERT Syntax:

CONVERT (data_type[(length)], expression [, style])

Parameters

Expression

Any valid expression.

Data_type

The target data type. This includes XML, bigint, and sql_variant. You cannot use an alias data type. For more information about the available data types, see Data types (Transact-SQL).

Length

An optional integer that specifies the length of the target data type. The default value is 30.

Style

Specifies how the CONVERT function converts an integer expression of expressions. If the style is NULL, NULL is returned. The scope is determined by the data_type. For more information, see the Remarks section.

return type

Returns the expression converted to data_type.

If expression is a date or time data type, the style can be one of the values shown in the following table. Other values are processed as 0. SQL Server uses the Kuwaiti algorithm to support the Arabic-style date format.

Without century digits (yy)	With century digits (yyyy)	Standard	Input/output (3)
	0 or 100 (up to)	Default	Mon dd yyyy hh:miam (or PM)
1	101	United States	Mm/dd/yyyy
2	102	Ansi	Yy.mm.dd
3	103	UK/France	Dd/mm/yyyy
4	104	Germany	Dd.mm.yy
5	105	Italy	Dd-mm-yy
6	106 (1)		DD Mon yy
7	107 (1)		Mon dd, yy
8	108		Hh:mi:ss
	9 or 109 (up to)	Default setting + milliseconds	Mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	United States	Mm-dd-yy
11	111	Japan	Yy/mm/dd
12	112	Iso	Yymmdd Yyyymmdd
	13 or 113 (up to)	Europe default setting + milliseconds	DD Mon yyyy hh:mi:ss:mmm (24h)
14	114		Hh:mi:ss:mmm (24h)
	20 or 120 (2)	ODBC specification	Yyyy-mm-dd Hh:mi:ss (24h)
	21 or 121 (2)	ODBC specification (with milliseconds)	Yyyy-mm-dd hh:mi:ss.mmm (24h)
	126 (4)	ISO8601	Yyyy-mm-ddthh:mi:ss.mmm (no spaces)
	127 (6, 7)	The ISO8601 with time zone Z.	Yyyy-mm-ddthh:mi:ss.mmmz (no spaces)
	130 (+)	Hijri (5)	DD Mon yyyy hh:mi:ss:mmmAM
	131 (2)	Hijri (5)	Dd/mm/yy Hh:mi:ss:mmmAM

Select CONVERT (varchar), GETDATE (), 8): 10:57:46
Select CONVERT (varchar), GETDATE (), 24): 10:57:47
Select CONVERT (varchar), GETDATE (), 108): 10:57:49
Select CONVERT (varchar), GETDATE (), 12): 060516
Select CONVERT (varchar), GETDATE (), 23): 2006-05-16

4.

SQL SERVER Common functions