Tech Learn

It accepts the two values, if both the values are matched then it will return NULL otherwise it will return first value. Examples-1: Declare @FisrtName varchar ( 100 ), @LastName varchar ( 100 ) Set @FisrtName = 'Rakesh' Set @LastName = 'Rakesh' Select NULLIF ( @FisrtName , @LastName ) As [NULLIF] Example-2: Declare @FisrtName varchar ( 100 ), @LastName varchar ( 100 ) Set @FisrtName = 'Rakesh' Set @LastName = 'Kalluri' Select NULLIF ( @FisrtName , @LastName ) As [NULLIF]

  --Create Source_Student   if object_id ( 'Source_Student' ) is null   create table Source_Student ( id int identity ( 1 , 1 ) , Name varchar ( 20 ) Marks int )                 --Insert Some Sample Data to Source_Student    insert Source_Student ( Name , Marks ) values ( 'Rakesh' , 500 )    insert Source_Student ( Name , Marks ) values ( 'Raju' , 400 )                 --Create Target_Student   if object_id ( 'Target_Student' ) is null   create table Target_Student ( id int , Name varchar ( 20 ), Marks int )                 --Insert Some Sample Data to Target_Student    insert Target_Student ( id , Name , Marks ) values ( 1 , 'Rakesh' , 600 )    in...

Concat function is used concatnating the values among the different arguments. Syntax: Concat([String1],[String2]..[StringN]);  DECLARE @FName VARCHAR ( 50 ), @LName VARCHAR ( 50 ); SET @FName = 'Rakesh' ; SET @LName = 'Kalluri' ; SELECT CONCAT ( @FName , ' ' , @LName ) as [FullName] ; Output: Rakesh Kalluri --Before SQL 2012 Versions we can use like this way DECLARE @FName VARCHAR ( 50 ), @LName VARCHAR ( 50 ); SET @FName = 'Rakesh' ; SET @LName = 'Kalluri' ; SELECT @FName + ' ' + @LName as [FullName] ; Output: Rakesh Kalluri --But Before SQL 2012 Versions there are 2 problems when we  contacting . --1.Need to replace NULL Value to Empty.if we directly concat NULL Value the  Entire  result also NULL. --Before 2012: DECLARE @FName VARCHAR ( 50 ), @LName VARCHAR ( 50 ); SET @FName = 'Rakesh' ; SELECT @FName + ' ' + @LName a...

Step-1: Create Table CREATE TABLE STUDENT ( ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY , STUDENTNAME VARCHAR ( 100 ) NOT NULL , LIVINGLOCATION VARCHAR ( 100 ) NULL ) Step-2: Insert Some Sample Data INSERT INTO STUDENT ( STUDENTNAME , LIVINGLOCATION ) SELECT 'Rakesh' , 'Hyderabad' UNION ALL SELECT 'Raju' , 'Delhi' UNION ALL SELECT 'Madhu' , 'Hyderabad' UNION ALL SELECT 'Naresh' ,NULL UNION ALL SELECT 'Venaktesh' , 'Chennai' Step-3: If we need to add DOJ column to Student table with default constraint , But all the Existing rows want to update Default GETDATE() , if WITH VALUES are not used all existing rows DOJ column should be NULL. ALTER TABLE STUDENT ADD DOJ DATETIME NULL DEFAULT GETDATE () WITH VALUES Step-4: SELECT * FROM STUDENT

Returns the starting position of first occurrence of pattern in a specified  expression . Syntax: Select Patindex(pattern char,expression char) returns int Note: It returns the integer value. It returns the zero (0) when you are specified position is not matched in the expression. Here is the simple string with postions for using entire examples in the Patindex Function. 1 2 3 4 5 6 7 8 9 10 11 12 L E A D F I R S T S Q L Example-1: SELECT PATINDEX ( '%a%' , 'LEADFIRSTSQL' ) [PATINDEX_POSITION] Example-2: SELECT PATINDEX ( '%S%' , 'LEADFIRSTSQL' ) [PATINDEX_POSITION] Example-3: SELECT PATINDEX ( '%_R_%' , 'LEADFIRSTSQL' ) [PATINDEX_POSITION] Example-4: SELECT PATINDEX ( '%[abcde]%' , 'LEADFIRSTSQL' ) [PATINDEX_POSITION] Here is the example any of the character ...

CREATE TABLE Student ( Id INT , Name VARCHAR ( 100 ) ) GO ALTER TABLE Student ADD Standard INT GO ALTER TABLE Student ADD Marks int GO Instead of writing 2 alter query's in above we done using single alter query. ALTER TABLE STUDENT ADD Standard INT , Marks INT GO SELECT * FROM Student GO DROP TABLE Student GO