postion of INDEX using Charindex and Pathindex

We have two function in SQL Server to find out position of INDEX

CHARINDEX( )  and PATHINDEX( ) , Both function are used to find out index postion and both have two arguments

With PATINDEX, must include percent signs before and after the pattern, unless for the pattern as the first (omit the first %) or last (omit the last %) characters in a column. For CHARINDEX, the pattern cannot include wildcard characters. The second argument is a character expression, usually a column name, in which Adaptive Server searches for the specified pattern.

Example of CHARINDEX:

Select CHARINDEX('Expresion',columnName) from TABLENAME



Examples of PATINDEX:


Select PATHINDEX('%Expresion%',columnName) from TABLENAME


Both return integer value , if not available , it returns Zero(0);
  

select max data from multiple column with respect id using PIVOT in SQL SERVER

Sometimes , we face a problem , how can retrive a max data from multiple column.
suppose ,  i have a table of student result.
In this table multiple column , ie, ROLLNUMBER , MATH,PHYSICS,CHEMISTRY ,TOTALMARKS.
it can be easilly calculated what is his  TOTALMARKS , but it cam be tuff , in which subject he got maximum marks??

Try some dummy code as follow
////////////////////TABLE CREATE////////////////////////////////////////////
CREATE TABLE [dbo].[TBLRESULT]([ROLLNUMBER] [bigint] NOT NULL,[MATH] [int] NULL,[PHYSICS] [int] NULL,[CHEMISTRY] [int]
)NULL ON [PRIMARY]

//////////////////////////////INSERT DATA    START QUERY////////////////////////////////////////
INSERT INTO TBLRESULT VALUES(ROLLNUMBER,MARKSOFmaths,MARKSOFphysics,MARKSOFchemistry)

 EXAMPLE --
  INSERT  INTO TBLRESULT VALUES(1004,12,0,9)

///////////////////////////////END QUERY///////////////////////////////////////////

SELECT *  FROM TBLRESULT

ROLLNUMBER	MATH	PHYSICS	CHEMISTRY
1001	10	12	14
1002	10	14	12
1003	14	12	0

Now use this query

////////////////////////////START QUERY//////////////////////////////////////////////////////////////

select row_number()over(partition by ROLLNUMBER order by MAXMARKS desc) as MAXMARKSID,ROLLNUMBER , SUBJECTS,MAXMARKSfrom (select ROLLNUMBER,SUBJECTS,MAXMARKS from (select ROLLNUMBER,MATH,PHYSICS,CHEMISTRY from TBLRESULT) ALIAS1unpivot
(MAXMARKS for SUBJECTS in(MATH,PHYSICS,CHEMISTRY)) AS ALIAS2 ) ALIAS3


////////////////////////////////END QUERY/////////////////////////////////////////////////////

Result like this
 

MAXMARKSID	ROLLNUMBER	MAXMARKS	MARKS
1	1001	14	CHEMISTRY
2	1001	12	PHYSICS
3	1001	10	MATH
1	1002	14	PHYSICS
2	1002	12	CHEMISTRY
3	1002	10	MATH
1	1003	14	MATH
2	1003	12	PHYSICS
3	1003	0	CHEMISTRY
1	1004	12	MATH
2	1004	9	CHEMISTRY
3	1004	0	PHYSICS

Now for Mximum obtained marks subjec can be obtained as


///////////////////////////START QUERY/////////////////////////////////////////////////////////////////////////////

WITH DUMMYTABLE as (select row_number()over(partition by ROLLNUMBER order by MAXMARKS desc) as MAXMARKSID,ROLLNUMBER , SUBJECTS,MAXMARKSfrom (select ROLLNUMBER,SUBJECTS,MAXMARKS from (select ROLLNUMBER,MATH,PHYSICS,CHEMISTRY from TBLRESULT) ALIAS1unpivot
(MAXMARKS for SUBJECTS in(MATH,PHYSICS,CHEMISTRY)) AS ALIAS2 ) ALIAS3)select ROLLNUMBER,SUBJECTS,MAXMARKS from DUMMYTABLE where DUMMYTABLE.MAXMARKSID =1

////////////////////////////////////////////END QUERY//////////////////////////////////////////////////////////////////
Result like this

ROLLNUMBER	SUBJECTS	MAXMARKS
1001	CHEMISTRY	14
1002	PHYSICS	14
1003	MATH	14