Functions in MS-SQL Server
Functions in SQL SERVER
We have 2 types of functions in Sqlserver. They are
1. System Functions---Built-in functions
2. User defined Functions
We can differentiate built-in functions following.
1. Single Row Functions
2. Group Functions
Single row Functions
Mathematical Functions
String Functions
Date and Time functions
Mathematical Functions
1. ABS
Select ABS (10)
Select ABS (-10)
Select ABS (0)
2. Ceiling Select Ceiling (15.6) Select Ceiling (15.2) Select Ceiling (15.0)
3. Floor Select floor (15.6) Select floor (15.2) Select floor (15.0)
4. Log (n) Select log (10) Select log (2)
5. Log10 Select log10 (10) Select log10 (2)
6. PI Select PI ()
7. Power (n, m) Select POWER (10, 3) Select POWER (2, 3)
8. RAND ([SEED])
Select RAND () Select RAND (100)
9. ROUND (n, length,[,Function]) Select ROUND (1546.561,2) Select ROUND (1546.561,1) Select ROUND (1546.561,0) Select ROUND (1546.561,-1) Select ROUND (1546.561,2,1) Select ROUND (1546.561,-2,1)
10. SIGN (n) Select SIGN (100) Select SIGN (0) Select SIGN (-100)
11. SQRT (n) Select SQRT (25)
12. SQUARE (n) Select SQUARE (5)
13. In Addition we have some tagnometric Functions like SIN, COS, TAN, ACOS, ASIN, ATAN
String Functions
1. ASCII(s) Select ASCII (‘A’)
2. CHAR (‘n’) Select CHAR (‘100’)
3. NCHAR (’n’) Select NCHAR (‘97’)
4. CHARINDEX (SearchExp, StringExp,[Start location]) Select CHARINDEX (‘o’,’HELLO’, 2)
5. LEFT(s, n) Select LEFT (‘HELLO’, 3)
6. RIGHT(S, N) Select RIGHT (‘HELLO’, 3)
7. SUBSTRING(S, Start, Length) Select SUBSTRING (‘HELLO’, 1, 5)
8. LEN(S) Select LEN (‘KRISH’)
9. LOWER(S) Select LOWER (‘kRIsh’)
10. UPPER(S) Select UPPER (‘JaNu’)
11. LTRIM(S) Select LEN (LTRIM (‘ HELLO’)) Select ‘Hello’+LTRIM (‘ WORLD’)
12. RTRIM(S) Select LEN (RTRIM (‘ HELLO ’)) Select ‘Hello’+ RTRIM(‘ WORLD ’)
13. REPLACE (S1, S2, S3) Select REPLACE (‘HELLO’,’L’,’X’)
14.
We have 2 types of functions in Sqlserver. They are
1. System Functions---Built-in functions
2. User defined Functions
We can differentiate built-in functions following.
1. Single Row Functions
2. Group Functions
Single row Functions
Mathematical Functions
String Functions
Date and Time functions
Mathematical Functions
1. ABS
Select ABS (10)
Select ABS (-10)
Select ABS (0)
2. Ceiling Select Ceiling (15.6) Select Ceiling (15.2) Select Ceiling (15.0)
3. Floor Select floor (15.6) Select floor (15.2) Select floor (15.0)
4. Log (n) Select log (10) Select log (2)
5. Log10 Select log10 (10) Select log10 (2)
6. PI Select PI ()
7. Power (n, m) Select POWER (10, 3) Select POWER (2, 3)
8. RAND ([SEED])
Select RAND () Select RAND (100)
9. ROUND (n, length,[,Function]) Select ROUND (1546.561,2) Select ROUND (1546.561,1) Select ROUND (1546.561,0) Select ROUND (1546.561,-1) Select ROUND (1546.561,2,1) Select ROUND (1546.561,-2,1)
10. SIGN (n) Select SIGN (100) Select SIGN (0) Select SIGN (-100)
11. SQRT (n) Select SQRT (25)
12. SQUARE (n) Select SQUARE (5)
13. In Addition we have some tagnometric Functions like SIN, COS, TAN, ACOS, ASIN, ATAN
String Functions
1. ASCII(s) Select ASCII (‘A’)
2. CHAR (‘n’) Select CHAR (‘100’)
3. NCHAR (’n’) Select NCHAR (‘97’)
4. CHARINDEX (SearchExp, StringExp,[Start location]) Select CHARINDEX (‘o’,’HELLO’, 2)
5. LEFT(s, n) Select LEFT (‘HELLO’, 3)
6. RIGHT(S, N) Select RIGHT (‘HELLO’, 3)
7. SUBSTRING(S, Start, Length) Select SUBSTRING (‘HELLO’, 1, 5)
8. LEN(S) Select LEN (‘KRISH’)
9. LOWER(S) Select LOWER (‘kRIsh’)
10. UPPER(S) Select UPPER (‘JaNu’)
11. LTRIM(S) Select LEN (LTRIM (‘ HELLO’)) Select ‘Hello’+LTRIM (‘ WORLD’)
12. RTRIM(S) Select LEN (RTRIM (‘ HELLO ’)) Select ‘Hello’+ RTRIM(‘ WORLD ’)
13. REPLACE (S1, S2, S3) Select REPLACE (‘HELLO’,’L’,’X’)
14.