Difference Types of Functions Used in SQL.
- Arithmetic Function
- Case Function
- IIF Function
- User-define Function
- String Function
Arithmetic Function
- ABS()
- FLOOR()
- EXP()
- POWER()
- SQRT()
- ACOS()
- ASIN()
- ATAN()
- ATN2()
- AVG()
- CEILING()
- COUNT()
- COS()
- COT()
- DEGREES()
- LOG()
- LOG10()
- MAX()
- MIN()
- PI()
- RADIANS()
- RAND()
- ROUND()
- SIGN()
- SQUARE()
- SUM()
- TAN()
1. ABS()
Description: To determine the absolute value of an integer supplied as an argument, use the SQL ABS() function.
Syntax: ABS(number)
Example: select ABS(-12.5) as absValue;
Result:
2. FLOOR()
Description: The greatest integer value that is less than or equal to a given number is the result of the FLOOR() function.
Syntax: FLOOR(number)
Example: select FLOOR(17.85) AS floorValue;
Result:
3. EXP()
Description: The result of the EXP() function is e increased to a given number’s power.
Syntax: EXP(number)
Example: select EXP(1) as expValue;
Result:
4. POWER()
Description: The value of a number raised to the power of another number is returned by the POWER() function.
Syntax: POWER(a, b)
Example: select POWER(2, 3) as num;
Result:
5. SQRT()
Description: The square root of a number is returned by the SQRT() function.
Syntax: SQRT(number)
Example: select SQRT(64) as sqrtnumber;
Result:
6. ACOS()
Description: The arc cosine of an integer is returned by the ACOS() function.
Syntax: ACOS(number)
Example: select ACOS(0.32) as acosValue;
Result:
7. ASIN()
Description: The arc sine of an integer is returned by the ASIN() function.
Syntax: ASIN(number)
Example: select ASIN(0.45) as asinValue;
Result:
8. ATAN()
Description: The arc tangent of an integer is returned by the ATAN() function.
Syntax: ATAN(number)
Example: select ATAN(2.5) as atanValue;
Result:
9. ATN2()
Description: The arc tangent of two numbers is returned by the ATN2() function.
Syntax: ATN2(a,b)
Example: select ATN2(0.60, 1) as atnValue;
Result:
10. AVG()
Description: The average value of an expression is returned by the AVG() function.
Syntax: AVG(expression)
Example: select AVG(Price) AS averagePrice from Product;
Result:
11. CEILING()
Description: The smallest integer value that is greater than or equal to a number is the result of the CEILING() function.
Syntax: CEILING(number)
Example: select CEILING(35.65) AS ceilValue;
Result:
12. COUNT()
Description: The number of records returned by a select query is returned by the COUNT() method.
Syntax: COUNT(expression)
Example: select COUNT(Product_ID) AS numberOfProducts FROM Product;
Result:
13. COS()
Description: The cosine of an integer is returned by the COS() function.
Syntax: COS(number)
Example: select COS(2) as cosValue;
Result:
14. COT()
Description: The cotangent of an integer is returned by the COT() function.
Syntax: COT(number)
Example: select COT(6) as cotValue;
Result:
15. DEGREES()
Description: A value in radians is converted to degrees using the DEGREES() function.
Syntax: DEGREES(number)
Example: select DEGREES(1.5) as degValue;
Result:
16. LOG()
Description: The LOG() function calculates the natural logarithm of a given number or the number’s logarithm to a given base.
Syntax: LOG(number,base)
Example: select LOG(2, 4) as logValue;
Result:
17. LOG10()
Description: The LOG10() method returns a number’s base-10 natural logarithm.
Syntax: LOG10(number)
Example: select LOG10(2) as logtenValue;
Result:
18. MAX()
Description: In a list of values, the MAX() method returns the highest value.
Syntax: MAX(expression)
Example: select MAX(Price) AS largestPrice FROM Product;
Result:
19. MIN()
Description: The minimum value out of a group of values is returned by the MIN() function.
Syntax: MIN(expression)
Example: select MIN(Price) AS smallestPrice FROM Product;
Result:
20. PI()
Description: he value of PI is returned by the PI() function.
Syntax: PI()
Example: select PI() as PIvalue;
Result:
21. RADIANS()
Description: A degree value is converted to radians using the RADIANS() function.
Syntax: RADIANS(number)
Example: select RADIANS(180) as radiansvalue;
Result:
22. RAND()
Description: A random number between 0 and 1 is produced using the RAND() function (exclusive).
Syntax: RAND(seed)
Example: select RAND(6) as randomNumber;
Result:
23. ROUND()
Description: A number is rounded to a predetermined number of decimal places using the ROUND() method.
Syntax: ROUND(number,decimals,operations)
Example: select ROUND(345.615, 2) AS RoundValue;
Result:
24. SIGN()
Description: The sign of an integer is returned by the SIGN() function.
Syntax: SIGN(number)
Example: select SIGN(255.5) as signNumber;
Result:
25. SIN()
Description: The sine of a number is returned by the SIN() function.
Syntax: SIN(number)
Example: select SIN(2) as sinNumber;
Result:
26. SQUARE()
Description: The SQUARE() function returns the square of a number.
Syntax: SQUARE(number)
Example: select SQUARE(4) as squNumber;
Result:
27. SUM()
Description: The sum of a group of values is determined by the SUM() function.
Syntax: SUM(expression)
Example: select SUM(price) AS TotalPrice FROM Product;
Result:
28. TAN()
Description: The tangent of an integer is returned by the TAN() function.
Syntax: TAN(number)
Example: select TAN(1.75) as tanValue;
Result:
Case Function
Description: Making multifaceted decisions is aided by case statements.
Syntax:
CASE WHEN con1 THEN res1 WHEN con2 THEN res2 WHEN con3 THAN res3 END;
Example:
select case when 25>30 then '25 is less than 30' when 20<10 then '20 is greater than 10' else '10 is equal to 20' end
Result:
IIF Function
Description: IIF function check the condition if it return true either false.
Syntax: IIF(Boolean expression,true_value,false_value)
Example: select Product_ID,Name,IIF(Price>500,’Price is greater than 500′, ‘Price is less than 500’) as ProductPrice FROM Product;
Result:
User-define Function
Description: There are two types of User-define function.
- Scalar Valued
- Table Valued
1. Scalar Valued
Description: A scalar value is always returned by a scalar valued function.
Syntax:
CREATE FUNCTION function_name(@param data_type,@param data_type…) RETURNS return_data_type AS BEGIN Function body RETURN VALUE END
Example:
CREATE FUNCTION add_num(@num int) RETURNS int AS BEGIN RETURN( @num + 10 ) End
select dbo.add_num(30) as Number;
Result:
2. Table Value
Description: Instead of returning a scalar, a table valued function does so.
Syntax:
CREATE FUNCTION function_name(@param data_type,@param data_type…) RETURNS table AS RETURN(SELECT column_list FROM table_name WHERE [condition])
Example:
CREATE FUNCTION select_Price(@price as int)
RETURNS table
AS
RETURN
(
SELECT*FROM Product WHERE Price=@price
)
SELECT *FROM dbo.select_Price(900);
Result:
String Function
- ASCII()
- CHAR()
- CHARINDEX()
- CONCAT()
- Concat with +
- CONCAT WS()
- DATALENGTH()
- DIFFERENCE()
- FORMAT()
- LEFT()
- LEN()
- LOWER()
- LTRIM()
- NCHAR()
- PATINDEX()
- QUOTENAME()
- REPLACE()
- REPLICATE()
- REVERSE()
- RIGHT()
- RTRIM()
- SOUNDEX()
- SPACE()
- STR()
- STUFF()
- SUBSTRING()
- TRANSLATE()
- TRIM()
- UNICODE()
- UPPER()
1. ASCII()
Description: Return ASCII value for the specific character using this function.
Syntax: ASCII(character)
Example:
select ASCII(Name) AS NumCodeOfFirstChar FROM Product;
Result:
2. CHAR()
Description: Return Character based on the ASCII code.
Syntax: CHAR(code)
Example:
select CHAR(65) AS CodeToCharacter;
Result:
3. CHARINDEX()
Description: This function return the position of searching substring in a string.
Syntax: CHARINDEX(substring,string,start)
Example:
select CHARINDEX('o', 'The Code Hubs') AS MatchPosition;
Result:
4. CONCAT()
Description: Adding string two or more using this function.
Syntax: CONCAT(string1,string2,string3,stringn…);
Example:
select concat('The',' code ','hubs' ) as concatValue;
Result:
5. Concat with +
Description: You can combine two or more strings with the + operator.
Syntax: string1 + string2 + stringn
Example:
select 'The' + ' ' + 'Code' + ' ' + 'Hubs!';
Result:
6. CONCAT WS()
Description: A separator is used by the CONCAT WS() function to combine two or more strings.
Syntax: CONCAT WS(sepretor,string1,string2,stringn)
Example:
select CONCAT_WS('-', 'The', ' Code', ' Hubs!') as WsConcat;
Result:
7. DATALENGTH()
Description: The number of bytes used to represent an expression is returned by the DATALENGTH() function.
Syntax: DATALENGTH(expression)
Example:
select DATALENGTH('TheCodeHubs') as DataLen;
Result:
8. DIFFERENCE()
Description: When two SOUNDEX values are compared, the DIFFERENCE() function produces an integer as a result. The integer value, which ranges from 0 to 4, represents the match between the two SOUNDEX values.
Syntax: DIFFERENCE(expression,expression)
Example:
select DIFFERENCE('TheCode', 'Hubs') as diff;
Result:
9. FORMAT()
Description: The FORMAT() function applies the provided format to a value.
Syntax: FORMAT(value,format,culture)
Example:
DECLARE @d DATETIME = '12/01/2018';
SELECT FORMAT (@d, 'd', 'hy-AM') AS 'Armenian - Armenia',
FORMAT (@d, 'd', 'zh-HK') AS 'Chinese - Hong Kong SAR',
FORMAT (@d, 'd', 'fr-CH') AS 'French - Switzerland';
Result:
10. LEFT()
Description: The LEFT() function retrieves a string’s first few characters (starting from left).
Syntax: LEFT(string,num_of_char)
Example:
SELECT LEFT('The CodeHubs', 3) AS GetString;
Result:
11. LEN()
Description: The length of a string is returned by the LEN() method.
Syntax: LEN(string)
Example:
SELECT LEN('TheCodeHubs');
Result:
12. LOWER()
Description: A string is converted to lowercase using the LOWER() method.
Syntax: LOWER(text)
Example:
SELECT LOWER('THE CODE HUBS!');
Result:
13. LTRIM()
Description: Leading spaces in a string are removed using the LTRIM() function.
Syntax: LTRIM(string)
Example:
SELECT LTRIM(' THE CODE HUBS') AS LeftTrimmedString;
Result:
14. NCHAR()
Description: Based on the number code, the NCHAR() function returns the Unicode character.
Syntax: NCHAR(number_code)
Example:
select NCHAR(65) AS numCodeToUnicode;
Result:
15. PATINDEX()
Description: The position of a pattern in a string is returned by the PATINDEX() method.
Syntax: PATINDEX(%pattern%,string)
Example:
select PATINDEX('%Code%', 'TheCodeHubs') as patIndexNum;
Result:
16. QUOTENAME()
Description: The Unicode string that is returned by the QUOTENAME() method has delimiters added to make it a legitimate SQL Server delimited identifier.
Syntax: QUOTENAME(string,quote_char)
Example:
select QUOTENAME('thecodehubs') as quoteNameStr;
Result:
17. REPLACE()
Description: The REPLACE() function substitutes a new substring for every instance of a substring found in a string.
Syntax: REPLACE(string, old_string, new_string)
Example:
select REPLACE('MheCodeHubs', 'M', 'T') as replaceName;
Result:
18. REPLICATE()
Description: A string is repeated by the REPLICATE() function a predetermined number of times.
Syntax: REPLICATE(string,Integer)
Example:
select REPLICATE('The Code Hubs', 3);
Result:
19. REVERSE()
Description: A string is reversed via the REVERSE() method, which then returns the outcome.
Syntax: REVERSE(string)
Example:
select REVERSE('The Code Hubs');
Result:
20. RIGHT()
Description: The RIGHT() method takes a string and extracts a number of characters
Syntax: RIGHT(string,number_of_char)
Example:
select RIGHT('The Code Hubs', 100) AS ExtractString;
Result:
21. RTRIM()
Description: A string’s trailing spaces are eliminated using the RTRIM() method.
Syntax: RTRIM(string)
Example:
select RTRIM('The code hubs ') AS rightTrimmedString;
Result:
22. SOUNDEX()
Description: To compare two expressions, the SOUNDEX() method returns a four-character code.
Syntax: SOUNDEX(expression)
Example:
select SOUNDEX('The'), SOUNDEX('Code');
Result:
23. SPACE()
Description: A string containing the requested amount of space characters is the result of the SPACE() function.
Syntax: SPACE(number)
Example:
select SPACE(10);
Result:
24. STR()
Description: A number is returned as a string by the STR() function.
Syntax: STR(number,Length,decimals)
Example:
select STR(190);
Result:
25. STUFF()
Description: Using a starting position that is given, the STUFF() function deletes a portion of a string and then inserts a new portion into the string.
Syntax: STUFF(string,start,Length,new_string)
Example:
select STUFF('The Code Hubs', 1, 3, 'ABC');
Result:
26. SUBSTRING()
Description: Extrame character from a string using This Function.
Syntax: SUBSTRING(string,start,length)
Example:
select SUBSTRING('The Code Hubs', 1, 3) AS ExtractString;
Result:
27. TRANSLATE()
Description: This function return first argument in the specified parameter after specified of the second and third argument.
Syntax: TRANSLATE(string, characters, translations)
Example:
select TRANSLATE('visaoninfot', 'visaoninfot', 'TheCodeHubs');
Result:
28. TRIM()
Description: This function remove the space from the string starting and ending.
Syntax: TRIM(space string space)
Example:
select TRIM(' The code hubs ');
Result:
29. UNICODE()
Description: Return The first character Unicode for the given expression string.
Syntax: UNICODE(character_expression)
Example:
select UNICODE('TheCodeHubs');
Result:
