SQL Server: Check a string contains a another string

In SQL Server we can check a string or character is in another string using CHARINDEX function.
CHARINDEX function will return the index of the starting letter of the sub string in the main string. It will return 0 if no match found.
Ex:

DECLARE @string1 VARCHAR(100)='This is Gopi Portal';  
SELECT CHARINDEX('Gopi',@string1)

Result:

We can use it in conditions as below

DECLARE @string1 VARCHAR(100)='This is Gopi Portal';
IF CHARINDEX('Gopi',@string1) > 0   
BEGIN 
   SELECT 'String found'
END
ELSE
BEGIN
   SELECT 'String Not found'
END

 Result:

 If string not found in the main string

DECLARE @string1 VARCHAR(100)='This is Gopi Portal';
IF CHARINDEX('Test',@string1) > 0   
BEGIN 
   SELECT 'String found' AS Result
END
ELSE
BEGIN
   SELECT 'String Not found' AS Result
END

 Result:

Note: We can also do it using Like operator as below. But it will not return the index of the string.

DECLARE @string1 VARCHAR(100)='This is Gopi Portal';
IF @string1 LIKE '%gopi%'
BEGIN 
   SELECT 'String found' AS Result
END
ELSE
BEGIN
   SELECT 'String Not found' AS Result
END

Happy Coding 😊!!

Read More»

SQL Function to remove HTML tags from Varchar field

Following is the function to remove HTML tags from a varchar field in SQL Server


CREATE FUNCTION [dbo].[RemoveHTML]
(
@vHTMLText VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @iFirst INT
    DECLARE @iLast INT
    DECLARE @iLength INT
    SET @iFirst = CHARINDEX('<',@vHTMLText)
    SET @iLast = CHARINDEX('>',@vHTMLText,CHARINDEX('<', @vHTMLText))
    SET @iLength = (@iLast - @iFirst) + 1
    WHILE @iFirst > 0 AND @iLast > 0 AND @iLength > 0
    BEGIN
        SET @vHTMLText = STUFF(@vHTMLText,@iFirst,@iLength,'')
        SET @iFirst =CHARINDEX('<',@vHTMLText)
        SET @iLast =CHARINDEX('>' ,@vHTMLText, CHARINDEX('<', @vHTMLText))
        SET @iLength = (@iLast - @iFirst) + 1
    END
    RETURN LTRIM(RTRIM(@vHTMLText))
END

Read More»

Calling SQL Server inbuilt functions in LINQ

Microsoft introduces a new class 'SqlFunctions', in .NET framework 4, to call the SQL Server inbuilt functions directly in LINQ Queries. It is available in "System.Data.Objects.SqlClient" namespace.

The SqlFunctions class contains methods that expose SQL Server functions to use in LINQ to Entities queries. When you use SqlFunctions methods in LINQ to Entities queries, the corresponding database functions are executed in the database.

Ex: The following example executes a LINQ to Entities query that uses the CharIndex method to return all contacts whose last name starts with "Si"

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())

{
      // SqlFunctions.CharIndex is executed in the database.
     var contacts = from c in AWEntities.Contacts
                           where SqlFunctions.CharIndex("Si", c.LastName) == 1
                           select c;
     foreach (var contact in contacts)
     {
          Console.WriteLine(contact.LastName);
      }
}

Database functions that perform a calculation on a set of values and return a single value (also known as aggregate database functions) can be directly invoked. Other canonical functions can only be called as part of a LINQ to Entities query. To call an aggregate function directly, you must pass an ObjectQuery to the function.

Ex: Below example calls the aggregate ChecksumAggregate method directly. Note that an ObjectQuery is passed to the function, which allows it to be called without being part of a LINQ to Entities query.

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
 {
      // SqlFunctions.ChecksumAggregate is executed in the database.
      decimal? checkSum = SqlFunctions.ChecksumAggregate(
            from o in AWEntities.SalesOrderHeaders
           select o.SalesOrderID);
      Console.WriteLine(checkSum);
 }

Note: To Call the Canonical Functions you need to used the EntityFunctions class.

Ex: The following example executes a LINQ to Entities query that uses the DiffDays method to return all products for which the difference between SellEndDate and SellStartDate is less than 365 days:

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
 {
    var products = from p in AWEntities.Products
                 where EntityFunctions.DiffDays(p.SellEndDate, p.SellStartDate) < 365
                  select p;
    foreach (var product in products)
    {
        Console.WriteLine(product.ProductID);
    }
}

For more information about SqlFunctions class, please check the below MSDN links

1) How to: Call Canonical Functions 

2) How to: Call Database Functions

3) SqlFunctions Class

4) Calling Functions in LINQ to Entities Queries

Read More»