Powered by Blogger.

Tuesday 10 October 2017

SQL FUNCTION

October 10, 2017 , ,
SQL Server you can use lots of built-in functions or you may create your own functions.
Here we will learn to use some of the most used built-in functions and in addition we will create our own function.
Built-in Functions-
SQL has many built-in functions for performing calculations on data. We have 2 categories of functions, namely Aggregate functions and scalar functions.
Aggregate functions return a single value, calculated from values in a column, while scalar functions return a single value, based on the input value.
Aggregate functions-examples:
  • AVG()-Returns the average value
  • STDEV()-Returns the standard deviation value
  • COUNT()-Returns the number of rows
  • MAX()-Returns the largest value
  • MIN()-Returns the smallest value
  • SUM()-Returns the sum
Scalar functions-examples:
  • UPPER()-Converts a field to upper case.
  • LOWER()-Converts a field to lower case.
  • LEN()-Returns the length of a text field.
  • ROUND()-Rounds a numeric field to the number of decimals specified.
  • GETDATE()-Returns the current system date and time.
  • REGEXP_SUBSTR()-The REGEXP_SUBSTR function use used to return the substring that matches a regular expression within a string.
  • LISTAGG():As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.
String Functions: Here are some useful functions used to manipulate with strings in SQL Server.
  • CHAR
  • CHARINDEX
  • REPLACE
  • SUBSTRING
  • LEN
  • REVERSE
  • LEFT
  • RIGHT
  • LOWER
  • UPPER
  • LTRIM
  • RTRIM  
Date and Time Functions: Here are some useful Date and Time functions in SQL Server.
  • DATEPART
  • GETDATE
  • DATEADD
  • DATEDIFF
  • DAY
  • MONTH
  • YEAR
  • ISDATE

Mathematics and Statistics Functions: Here are some useful functions for mathematics and statistics in SQL Server.

  • COUNT
  • MIN, MAX
  • COS, SIN, TAN
  • SQRT
  • STDEV
  • MEAN
  • AVG 

AVG():

The AVG() function returns the average value of a numeric column.
Syntax:

SELECT AVG(column_name) FROM table_name;
Example: We Have a Products Table.

+-----------+---------------+------------+------------+------+-------+-----+-------+
| ProductID | ProductName   | SupplierID | CategoryID |        Unit        | Price |
+-----------+---------------+------------+------------+------+-------+-----+-------+
|      1    |     Chais     |    1       |     1      | 10 boxes * 20 bags |   18  |
|      2    |     Chang     |    1       |     1      | 24 -12 oz bottles  |   19  |
|      3    | Aniseed Syrnp |    1       |     1      |12 -500 ml bottles  |   18  |
+-----------+---------------+------------+------------+------+-------+-----+-------+
We want to find the average grade for a specific Product:

SELECT AVG(Price) AS AvgGrade FROM Products where SupplierID=1;
Output:
+-----------+
|  AvgGrade |
+-----------+
| 15.6667   |
+-----------+

STDEV(): The STDEV in SQL simply returns the Standard Deviation of total number of records present in the specified column.
Syntax:
Select STDEV(Column_Name) from table_name;
Example: We Have a Products Table.
+-----------+------------------+------------+------------+--------------------+-------+
| ProductID | ProductName      | SupplierID | CategoryID |        Unit        | Price |
+-----------+------------------+------------+------------+--------------------+-------+
|     38    | Cote de Blaye    |    18      |     1      |12 -75 d bottles    | 263.5 |
|     39    | Chartreuse verte |    18      |     1      |12 -750 cc bottles  |   18  |
+-----------+------------------+------------+------------+--------------------+-------+
We want to find the Standard Deviation for a specific Product:
Select STDEV(Price) AS AveragePrice FROM Products;
Output:
+------------------+
|   AveragePrice   |
+------------------+
| 173.594714781297 |
+------------------+

COUNT(): The COUNT() function returns the number of rows that matches a specified criteria. The COUNT(column_name) function returns the number of values (NULL values will not be Counted) of the specified column.
Syntax:
Select COUNT(Column_Name) from table_name;
The COUNT(*) function returns the number of records in a table.
Select COUNT(*) from table_name;
Example: We Have a Products Table.
+-----------+---------------+------------+------------+------+-------+-----+-------+
| ProductID | ProductName   | SupplierID | CategoryID |        Unit        | Price |
+-----------+---------------+------------+------------+------+-------+-----+-------+
|      1    |     Chais     |    1       |     1      | 10 boxes * 20 bags |   18  |
|      2    |     Chang     |    1       |     1      | 24 -12 oz bottles  |   19  |
|      3    | Aniseed Syrnp |    1       |     2      |12 -500 ml bottles  |   10  |
+-----------+---------------+------------+------------+------+-------+-----+-------+

SELECT COUNT(*) as NumbersofCustomers FROM Products;
Output:
+------------------------+
|   NumbersofCustomers   |
+------------------------+
|             3          |
+------------------------+

MAX():
Syntax:
Select MAX(Column_Name) from table_name;
The MAX(Column_Name) function returns the number of records in a table.
Select MAX(Column_Name) from table_name;
Example: We Have a Products Table.
+-----------+---------------+------------+------------+------+-------+-----+-------+
| ProductID | ProductName   | SupplierID | CategoryID |        Unit        | Price |
+-----------+---------------+------------+------------+------+-------+-----+-------+
|      1    |     Chais     |    1       |     1      | 10 boxes * 20 bags |   18  |
|      2    |     Chang     |    1       |     1      | 24 -12 oz bottles  |   19  |
|      3    | Aniseed Syrnp |    1       |     2      |12 -500 ml bottles  |   10  |
+-----------+---------------+------------+------------+------+-------+-----+-------+

SELECT MAX(Price) as maxC FROM Products;
Output:
+----------+
|   maxC   |
+----------+
|    19    |
+----------+

MIN():
Syntax:
Select MIN(Column_Name) from table_name;
The MIN(Column_Name) function returns the number of records in a table.
Select MIN(Column_Name) from table_name;
Example: We Have a Products Table.
+-----------+---------------+------------+------------+------+-------+-----+-------+
| ProductID | ProductName   | SupplierID | CategoryID |        Unit        | Price |
+-----------+---------------+------------+------------+------+-------+-----+-------+
|      1    |     Chais     |    1       |     1      | 10 boxes * 20 bags |   18  |
|      2    |     Chang     |    1       |     1      | 24 -12 oz bottles  |   19  |
|      3    | Aniseed Syrnp |    1       |     2      |12 -500 ml bottles  |   10  |
+-----------+---------------+------------+------------+------+-------+-----+-------+

SELECT MIN(Price) as minC FROM Products;
Output:
+----------+
|   minC   |
+----------+
|    10    |
+----------+
REGEXP_SUBSTR():
Here are some  scenario-based statements.
 regexp_substr(str, '[a-z]{6}')   /* returns the first string of 6 characters */  
regexp_substr(str, '[0-9]+')      /* returns the first matching numbers */  
regexp_substr(str, '[a-z].*$')     /* returns the first letter followed by all other characters */  
regexp_substr(str, '/[a-z].*$')      /* returns / followed by a letter then all other characters */  


SELECT REGEXP_SUBSTR ('1,2,3,4,5,6', '[^,]+', 1, level) as REGEXPSUBSTR 
      FROM DUAL 
connect by level <= length(REGEXP_REPLACE('1,2,3,4,5,6','[^,]+'))+1;
Output:
+-----------------------+
|   REGEXPSUBSTR |
+-----------------------+
|          1            |
|          2            |
|          3            |
|          5            |
|          6            |
+------------------------+

LISTAGG():
 we have one Table Name is DemoListagg and column name C_name.
+-----------------------+
|       C_Name              |
+-----------------------+
|          1            |
|          2            |
|          3            |
|          5            |
|          6            |
+------------------------+
Example:

select LISTAGG(C_name,', ' ON OVERFLOW TRUNCATE WITH COUNT)
               WITHIN GROUP (ORDER BY C_name) from DemoListagg;
Output:
               1,2,3,4,5,6,

Subscribe to: Posts ( Atom )