Sql Server

Check If A String Contains A Substring In SQL Server

Check If A String Contains A Substring In SQL Server

Check If A String in Sql

SQL provide CHARINDEX() function Or LIKE Predicate to  check if the string contains a specific substring

Method 1 - Using CHARINDEX() function
used to find a certain word or substring within a larger text and returns the first match it found.
It will return 0 if no words are detected (zero).

CHARINDEX ( SearchString,WholeString[ , startlocation ] )

Declare @mainString nvarchar(100)='Deepak Kmr Talwar'
if CHARINDEX('Kmr',@mainString) > 0
   select 'Found' As Result
    select 'Not Found' As Result

Method 2 - Using LIKE Predicate

You can search within a string or content for a substring using the LIKE predicate operator.
To find one additional character and one character, respectively, the LIKE operator is coupled with % and (underscore).
The % operator can be used to locate a substring.

DECLARE @WholeString VARCHAR(50)
DECLARE  @ExpressionToFind VARCHAR(50)
SET @WholeString = 'Deepak Kmr Talwar'
SET @ExpressionToFind = 'Kmr'

IF @WholeString LIKE '%' + @ExpressionToFind + '%'
    PRINT 'Found'
    PRINT 'Not Found'

The WHERE clause of SELECT, UPDATE, and DELETE queries can also use this LIKE technique.

SELECT [EmployeeID]
FROM [Employees]
WHERE [FirstName] LIKE '%Deepak%'

Deepak Talwar

Deepak Talwar

Principal Software Engineer and Full Stack Developer with 12+ years of Professional Experience in Microsoft Technologies. Hands on experience with C#, ASP.NET, ASP.NET MVC, ASP.NET Core, WebAPI, Linq, Entity Framework, MS-SQL, NoSql, Javascript, Angular, jQuery, AWS, Azure, ReactJs, AngularJs, Laravel, Codeingiter, Serenity. VBA, Software Architecture, Web Design and Development.

Related Post

About Us

Community of IT Professionals

A Complete IT knowledgebase for any kind of Software Language, Development, Programming, Coding, Designing, Networking, Hardware and Digital Marketing.