### PATINDEX AND CHARINDEX IN SQL SERVER WITH EXAMPLE

• CHARINDEX and PATINDEX functions return the starting position of a specified pattern.
• The difference between CHARINDEX and PATINDEX are, In CHARINDEX you can specify starting position,in PATINDEX not possible to specify starting position, it will check pattern matching start to END and return the first matched position

DECLARE @TESTINDEX AS TABLE (ID INT ,Remarks VARCHAR(50))

INSERT INTO @TESTINDEX VALUES(1,'Remark:Employee:1 -Raju')
INSERT INTO @TESTINDEX VALUES(2,'Remark:TEmployee:2 -Manu')
INSERT INTO @TESTINDEX VALUES(3,'Remark-TEmployee:2 -Jhon')
INSERT INTO @TESTINDEX VALUES(4,'Remark:DailyWAge:2 -Jhon')
INSERT INTO @TESTINDEX VALUES(5,'Remark-Da:Employee With DailyWage:1-Employee -Jhon')

/*Select Starting Position Using PatIndex*/
SELECT PATINDEX('%EMPLOYEE%',Remarks) AS StartingPosition,* FROM @TESTINDEX

/*Select Starting Position Using CharIndex*/
SELECT CHARINDEX('EMPLOYEE', Remarks) AS StartingPosition,* FROM @TESTINDEX

/*
Filtering Data by specifying Starting Position in CharIndex.
Here 13 is the specified starting Position
check pattern matching after 13th  Position
*/
SELECT CHARINDEX('EMPLOYEE', Remarks,13),* FROM @TESTINDEX
WHERE CHARINDEX('EMPLOYEE', Remarks,13) > 20

### Ranking Functions In SQL Server

ROW_NUMBER ()
Row number returns continues number of row  with in a partition, starting at 1 for 1st Row
RANK ()
Ranking the each row with in the partition ,chance for gap depending partition
DENSE_RANK ()
Ranking the each row with in the partition without any gaps, means  continues ranking
NTILE()
Ranking the total records in a specific number of groups

CREATE TABLE TEST (NAME VARCHAR(20),MARK INT)

INSERT INTO TEST VALUES('RAM',10)
INSERT INTO TEST VALUES('RAJU',10)
INSERT INTO TEST VALUES('VINEETH',10)
INSERT INTO TEST VALUES('RANITH',50)
INSERT INTO TEST VALUES('TELMI',50)
INSERT INTO TEST VALUES('JHON',100)

SELECT
ROW_NUMBER() OVER (ORDER BY MARK) AS 'SLNO',
RANK() OVER (ORDER BY MARK) AS 'RANK',
DENSE_RANK() OVER (ORDER BY MARK) AS 'DENSE RANK',
NTILE(2) OVER (ORDER BY MARK) AS 'NTILE'
,NAME,MARK
FROM TEST

OUT PUT

### MERGE STATEMENT IN SQL SERVER

DECLARE @TEST TABLE(DATA VARCHAR(20),ID INT)
INSERT INTO @TEST VALUES('RAJU',1)
INSERT INTO @TEST VALUES('RANI',2)
INSERT INTO @TEST VALUES('MANU',3)

DECLARE @MERGTEST TABLE(DATA VARCHAR(20),ID INT)
INSERT INTO @MERGTEST VALUES('RAJU',1)
INSERT INTO @MERGTEST VALUES('RANI',2)
INSERT INTO @MERGTEST VALUES('JONSON',4)

SELECT 'NON MERGED TABLE TEST',* FROM @TEST
SELECT 'NON MERGED TABLE MERGTEST',* FROM @MERGTEST

MERGE INTO @TEST AS TEST
USING(SELECT DATA,ID FROM @MERGTEST) AS MERGTEST(DATA,ID)
ON TEST.DATA=MERGTEST.DATA AND TEST.ID=MERGTEST.ID
WHEN NOT MATCHED BY TARGET THEN
INSERT(DATA,ID) VALUES(MERGTEST.DATA,MERGTEST.ID);

SELECT 'MERGED TABLE TEST',* FROM @TEST

### ABS() FUNCTION IN SQL SERVER

The ABS function is used for return positive value of numeric expression or to remove Minus value from
numeric expression.

### Passing Negative value to  ABS Function

SELECT ABS(-123.25)   ---Output  123.25

### Passing Positive value to  ABS Function

SELECT ABS(123.25)  ---Output  123.25