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

FIND CPU_BUSY, PACK_RECEIVED, PACK_SENT, LANGUAGE VERSIONS IN SQL SERVER

SELECT SYSTEM_USER USERNAME,@@VERSION VERSION,@@CPU_BUSY CPU_BUSY,

@@PACK_RECEIVED PACK_RECEIVED,@@PACK_SENT PACK_SENT,@@LANGUAGE  LANGUAGE

Ranking Functions In SQL Server

ROW_NUMBER ()

Row number returns continues number of row  with in a partition, starting at 1 for 1^st 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