Prime Study Hub

COL_NAME is a predefined SQL server function that returns the name of a table column using table and column identification numbers. COL_NAME (table_id, column_id) COL_NAME function takes two parameters which is table and column identification numbers like specified in the above statement. Let's look into an example of AdventureWorks2019 database, SELECT TOP (1000) [BusinessEntityID] ,[PersonType] ,[NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[Suffix] ,[EmailPromotion] ,[AdditionalContactInfo] ,[Demographics] ,[rowguid] ,[ModifiedDate] FROM [AdventureWorks2019].[Person].[Person] Here is the above statement result set, Let's see how to retrieve the column name FirstName using the COL_NAME function, SELECT COL_NAME(OBJECT_ID('Person.Person'), 5) AS ColumnName; /* COL_NAME (table_id, column_id) */ The OBJECT_ID function returns the identification number of a specified tab...

CONTAINS is a predicate used to perform a full-text search in one or more full-text indexed columns. Let's look into an example, SELECT [Description] FROM [Sales].[SpecialOffer] WHERE CONTAINS([Description], 'discount'); /* CONTAINS(Column_Name, Search_Text) */ The above statement returns the data which has  discount  word in column  [Description] .  And CONTAINS predicate is not case sensitive. CONTAINS text search is very quick and easy to search a word or phrase.   Note: CONTAINS full-text search works in a full-text indexed column. If you face any errors, have a look at this link here . Using CONTAINS with <multiple columns> SELECT [Description],[Type] FROM [Sales].[SpecialOffer] WHERE CONTAINS(([Description],[Type]), 'discount'); /* CONTAINS((Column_Name1,Column_Name2,...), Search_Text) */ The above CONTAINS query searches the word discount in both [Description] and [Type] columns and returns the result that matches the exact word. Using C...

 IS NOT NULL is the opposite of the IS NULL predicate, which is useful to retrieve the values that doesn't have NULL value in an expression. Let's look into an example of AdventureWorks2019 database, SELECT [BusinessEntityID] ,[DepartmentID] ,[ShiftID] ,[StartDate] ,[EndDate] ,[ModifiedDate] FROM [AdventureWorks2019].[HumanResources].[EmployeeDepartmentHistory] WHERE [EndDate] IS NOT NULL Above statement returns the  EmployeeDepartmentHistory which doesn't have NULL EndDate values.

 IS NULL is a predicate which is useful to identify the expression has NULL values.  Let's look into an example of AdventureWorks2019 database, SELECT [BusinessEntityID] ,[DepartmentID] ,[ShiftID] ,[StartDate] ,[EndDate] ,[ModifiedDate] FROM [AdventureWorks2019].[HumanResources].[EmployeeDepartmentHistory] WHERE [EndDate] IS NULL Above statement returns the EmployeeDepartmentHistory which has NULL EndDate value.