SQL FUNCTIONS

-- SQL FUNCTION

--Returns the right part of a character string with the specified number of characters

SELECT RIGHT('SQLDBA567',3)

--Returns the left part of a character string with the specified number of characters.

SELECT LEFT('SQLDBA567',6)

--Returns the number of characters of the specified string expression, excluding trailing blanks.

SELECT LEN('SQLDBA567')

--Replaces all occurrences of a specified string value with another string value.

SELECT REPLACE('S2Q2L2D2B_2A252627','2','')

---Searches an expression for another expression and returns its starting position if found.

SELECT charindex('A',  'SQLDBA567' )  

--Returns the starting position of the first occurrence of a pattern in a specified expression,

--or zeros if the pattern is not found, on all valid text and character data types.

SELECT PATINDEX ('%DB%', 'SQLDBA567')

--Returns part of a character, binary, text, or image expression in SQL Server.

SELECT SUBSTRING('SQLDBA567-Developer', 4,3)

--The STUFF function inserts a string into another string. It deletes a specified length of characters in the

--first string at the start position and then inserts the second string into the first string at the start position.

SELECT STUFF ('SQLDevDBA', 4,3,'')

--Returns a character expression after converting uppercase character data to lowercase.

SELECT LOWER('SQLDBA') 

--Returns a character expression with lowercase character data converted to uppercase.

SELECT UPPER ('sqldba') 

--Returns a character expression after it removes leading blanks.

SELECT LTRIM('      SQLDBA567   ') 

--Returns a character string after truncating all trailing blanks.

SELECT RTRIM('     SQLDBA567   ') 

--Returns character data converted from numeric data.

SELECT STR(12345.6789, 6, 1);

DECLARE @TABLE TABLE

(COL1 INT)

INSERT INTO @TABLE VALUES (15),(20),(30)

SELECT * FROM @TABLE

SELECT AVG(COL1) AS AVERAGE FROM @TABLE     --Gives avgerage for that COL1

SELECT MIN(COL1) AS MINIMUM FROM @TABLE     --Gives minimum value for that COL1

SELECT MAX(COL1) AS MAXIMUM FROM @TABLE     --Gives maximum value for that COL1

SELECT SUM(COL1) AS TOTAL FROM @TABLE       --Gives Total sum value for that COL1

SELECT COUNT(COL1)  TOTAL_COUNT FROM @TABLE --Gives number of row in that COL1

SELECT COUNT(COL1)  TOTAL_COUNT FROM @TABLE

--DIFFERENCE BETWEEN ISNULL AND COALESCE

DECLARE @TABLE table

(Col VARCHAR(100))

INSERT INTO @TABLE VALUES (NULL),('SECOND VALUE')

SELECT * FROM @TABLE

SELECT ISNULL (COL, 'DEFAULT VALUE') AS VALUE  FROM @TABLE

DECLARE @TABLE TABLE

(COL1 VARCHAR(25), COL2 VARCHAR(25), COL3 VARCHAR(25))

INSERT INTO @TABLE VALUES (NULL, NULL,'THIRD VALUE'), ('FIRST VALUE', NULL, NULL), (NULL,'SECOND VALUE', NULL)

SELECT * FROM @TABLE

SELECT COALESCE (COL1, COL2, COL3) FROM @TABLE

--WHAT IS RANK(), ROW_NUMBER(), DENSE_RANK(), NTILE() WITH E.G

WITH TABLE1 (CHECK_ID, ID_NO)

     AS (SELECT 7,3 UNION ALL

         SELECT 7,7 UNION ALL

         SELECT 7,7 UNION ALL

               SELECT 7,9 UNION ALL

         SELECT 7,9)

SELECT *,

       RANK() OVER(PARTITION BY CHECK_ID ORDER BY ID_NO)       AS 'RANK',

       ROW_NUMBER() OVER(PARTITION BY CHECK_ID ORDER BY ID_NO) AS 'ROW_NUMBER',

       DENSE_RANK() OVER(PARTITION BY CHECK_ID ORDER BY ID_NO) AS 'DENSE_RANK',

          NTILE(2) OVER(PARTITION BY CHECK_ID ORDER BY ID_NO) AS 'Ntile'

FROM   TABLE1