SQL Contains String | SQL Contains String Examples

In my previous article i have given the details about different SQL statements with real life examples. There is always question in mind about the SQL Contains Strings and how to use the SQL Contains String ? In this article i would like to give you pattern matching techniques for SQL which i have already covered in historical articles.In this article i would like to give you multiple examples of SQL Contains String.

SQL Contains String Examples :

In this section i want to give you multiple real life examples of SQL Contains. I would like to start with simple LIKE Operator examples.The Like operator is most commonly used operator for pattern matching in SQL.

I would like to start with simple examples of SQL Contains String,

Scenario 1 : If user wants to Find out the customers Whose name contains ‘Amit’.

Solution 1 : Using LIKE Operator

SQL Query Logic : Here user needs to use like operator to check whether there are people whose name contains ‘Amit’.

SQL Query :

Select * from Customer Where First_name LIKE ‘%Amit%’;

The above query will fetch the data from Customer table where First_Name contains String as ‘Amit’.

Solution 2: Using IN Operator :

The same query user can write using IN operator.

SQL Query :

Select * from Customer where First_Name IN (‘Amit’);

Solution 3 : Microsoft SQL Server Solution :

In MS SQL we have the Contains function as well. The contains function is faster than LIKE operator. So it is always recommended to use the Contains function to check the patterns.

SQL Query :

Select * from Customer where CONTAINS(First_name,’Amit’);

The above query will fetch the customer data where First_name Contains string as ‘Amit’

Solution 4 : In PL SQL Building Block Or T-SQL block

User can check whether the String is there with using PL SQL Building blocks as well. For that we need to use CHARINDEX function.

Declare @Customer_Name nvarchar(100)=’Amit Anil Shiravadekar’  

if CHARINDEX(‘Amit’,@Customer_Name) > 0 

begin  

   select ‘Find’ As Result  

end  

else  

    select ‘Not Find’ As Result 

end;

The above program will give the output as ‘Find’ as it will find String as Amit.

Scenario 2 : If user wants to find out the Customer names whose name contains ‘Amit’ and Surname contains ‘Sharma’.

Solution 1 : In this case user needs to use AND operator with LIKE.

SQL Query :

Select * from Customer where First_Name LIKE ‘%Amit%’ AND Last_Name LIKE ‘%SHARMA%’;

The above query will give the Customer data for First_name is ‘Amit’ and Last_Name is ‘Sharma’.

Solution 2 : Using CHARINDEX function.

User can also try to use CHARINDEX function to find out the patterns correctly.

SQL Query :

Select * from Customer

Where CHARINDEX(‘Amit’,First_Name) > 0

AND CHARINDEX(‘Sharma’,Last_Name) > 0;

These are some real life examples of SQL Contains String where user can check the patterns of the String and fetch output accordingly.

Leave a Reply

Your email address will not be published. Required fields are marked *