# SQL Functions

### Difference Types of Functions Used in SQL.

• Arithmetic Function
• Case Function
• IIF Function
• User-define Function
• String Function

Arithmetic Function

1. ABS()
2. FLOOR()
3. EXP()
4. POWER()
5. SQRT()
6. ACOS()
7. ASIN()
8. ATAN()
9. ATN2()
10. AVG()
11. CEILING()
12. COUNT()
13. COS()
14. COT()
15. DEGREES()
16. LOG()
17. LOG10()
18. MAX()
19. MIN()
20. PI()
22. RAND()
23. ROUND()
24. SIGN()
25. SQUARE()
26. SUM()
27. 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:

Description: A degree value is converted to radians using the RADIANS() function.

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.

1. Scalar Valued
2. 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```

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

1. ASCII()
2. CHAR()
3. CHARINDEX()
4. CONCAT()
5. Concat with +
6. CONCAT WS()
7. DATALENGTH()
8. DIFFERENCE()
9. FORMAT()
10. LEFT()
11. LEN()
12. LOWER()
13. LTRIM()
14. NCHAR()
15. PATINDEX()
16. QUOTENAME()
17. REPLACE()
18. REPLICATE()
19. REVERSE()
20. RIGHT()
21. RTRIM()
22. SOUNDEX()
23. SPACE()
24. STR()
25. STUFF()
26. SUBSTRING()
27. TRANSLATE()
28. TRIM()
29. UNICODE()
30. 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:

## Subscribe

Select Categories