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