Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

Finding a string value in a SQL Server table

MSSQLTips author Tim Ford By:   |   Read Comments (3)   |   Related Tips: More > Functions - User Defined UDF

Problem
A SQL Server/Web Application Developer in my company approached me yesterday with a dilemma.  He needed to be able to find a phrase within a specific field in a SQL Server database he was developing.  If he received a "hit" then the process was to return the related value in a field within a parent table.  This is a typically simple process that can be handled with the use of the T-SQL CHARINDEX() function, however there were certain caveats in his requirements that would not allow CHARINDEX() to be the complete solution to his problem.  He needed to only consider situations where the value of the field started with the phrase or if the phrase was the beginning of a word in the value of the search field it was a valid hit.  Read on and I'll explain further.

Solution
The web application being developed was for a medical website under which a user could select a phrase and search on various symptoms to return a related record in a table for possible malady.  A phrase in this case could be multiple words or a single letter.  Therefore if you selected your search phrase to be "B" and there were values in the search field of "Bone Mass", "Thumb Pain", or "Tumor, Benign" the results would be a positive hit on "Bone Mass" (because the value starts with the letter B) and "Tumor, Benign" (because it is preceded by a space).  Even though the word "Thumb" includes the letter B in the search criteria it is omitted because it is, essentially, not the first letter of a word in the value.

Let's dismiss the medical jargon for a second and take a look at an example where I relate the content back to SQL Server.  For the sake of this example I will go on the basis that we have two tables: ##ProductChild (with values pertaining to relational database management systems) and ##ProductParent, which will be used to store just two records that state whether a given record in ##ProductChild is a SQL Server database system or not.  We will use "SQL" as our search criteria.

--Create Product Table and enter in sample values
CREATE TABLE ##ProductChild (ID INTChildDescription VARCHAR(100), FKID INT)

INSERT INTO ##ProductChild (IDChildDescriptionFKID
VALUES (1'SQL Server'10)

INSERT INTO ##ProductChild (IDChildDescriptionFKID
VALUES (2'MySQL'20)

INSERT INTO ##ProductChild (IDChildDescriptionFKID
VALUES (3'Oracle'30)

INSERT INTO ##ProductChild (IDChildDescriptionFKID
VALUES (4'SQL Server 2000'10)

INSERT INTO ##ProductChild (IDChildDescriptionFKID
VALUES (4'SQL Server 2005'10)

INSERT INTO ##ProductChild (IDChildDescriptionFKID
VALUES (4'SQL Server 2008'10)

INSERT INTO ##ProductChild (IDChildDescriptionFKID
VALUES (5'Enterprise Edition SQL Server 2008'10)

--Create Parent SQL Server Yes/No table and enter in sample values
CREATE TABLE ##ProductParent (ID INTItem VARCHAR(100))

INSERT INTO ##ProductParent (IDItem
VALUES (10'SQL Server')

INSERT INTO ##ProductParent (IDItem
VALUES (20'Non-SQL Server'
)

At this point if we query each table we will receive the following results:

SELECT IDChildDescriptionFKID 
FROM ##ProductChild
ORDER BY 
ID

SELECT IDItem 
FROM ##ProductParent
ORDER BY 
ID

There are two different approaches that can be taken to perform this query from this point forward.  I will highlight both options and note now that the actual execution plan and execution times were identical.

Option 1: Use the CHARINDEX() Function

Now, using these sample tables we will search for all records in ##ProductChild where ChildDescription either starts with "SQL" or contains a word that starts with "SQL".  We will ignore any results that do not meet this criteria, even if "SQL" appears in the field within a given word.  I'll do so by breaking a rule I try to live by: no functions in the WHERE clause.  These tables are extremely small.  In the real-world scenario that prompted this article, we are also not dealing with more than a couple thousand records either.

--Declare necessary variables
DECLARE @searchstring VARCHAR(20)
DECLARE @lensearch smallint

--Set values for search 
SELECT @searchstring 'SQL'
SELECT @lensearch LEN(@searchstring)

--Perform Search
SELECT DISTINCT P.IDP.Item
FROM ##ProductParent INNER JOIN ##ProductChild ON P.ID C.FKID
WHERE 
   
C.ChildDescription LIKE @searchstring '%'
   
OR
   
CHARINDEX(' ' @searchstringC.ChildDescription1) > 0
ORDER BY P.ID
   
DROP TABLE ##ProductChild
DROP TABLE 
##ProductParent

 

Taking a closer look at this code it is extremely straight-forward compared to the ideas he and I tossed about: cursors, loops, all sorts of madness.  We end up breaking the two positive hit possibilities into two parts of an OR statement:

  • If ##ProductChild.ChildDescription starts with "SQL": C.ChildDescription LIKE @searchstring
  • If ##ProductChild.ChildDescription contains a word that starts with "SQL": ('' + @searchstring , C.ChildDescription , 1) > 0

CHARINDEX() is the T-SQL function used to search for a given value within a field.  It accepts three parameters:

  • Search Value
  • Field to Search
  • Starting Position in Field

In the sample code I append a space onto the beginning of the search string so as to force the CHARINDEX function to ignore any hits within words in the ChildDescription table.

Option 2: Use the LIKE Condition

Instead of using CHARINDEX() to account for "hits" where the search string is preceded by a space we can instead simply append a space and '%' wildcard before the @searchstring variable as shown below.  Ultimately this is probably the better solution as the format of the programming is consistent between criteria being searched upon.  There is also one less variable and therefore less overhead involved in this code block.  Simply put, the code is more intuitive from a readability standpoint.

--Declare necessary variables 
DECLARE @searchstring VARCHAR(20

--Set values for search 
SELECT @searchstring 'SQL'

--Perform Search 
SELECT DISTINCT P.IDP.Item 
FROM ##ProductParent INNER JOIN ##ProductChild ON P.ID C.FKID 
WHERE 
   
C.ChildDescription LIKE @searchstring '%'
   
OR 
   
C.ChildDescription LIKE '% ' @searchstring '%'
ORDER BY P.ID 

DROP TABLE ##ProductChild 
DROP TABLE 
##ProductParent

 

End Result:

Regardless of which option I use, the results are identical.  I get valid hits on 4 records in the  ##ProductChild.ChildDescription and therefore receive these results for the query:

 

If I was to search based upon "My" I'd receive the following results, due to the fact that MySQL is not a Microsoft SQL Server product.

 

Of course the proper process is to encapsulate this into a stored procedure.  Using what we just learned this is the final result:

CREATE PROCEDURE uspSearchWord @searchstring VARCHAR(100AS
SELECT DISTINCT 
P.IDP.Item 
FROM ##ProductParent INNER JOIN ##ProductChild ON P.ID C.FKID 
WHERE 
   
C.ChildDescription LIKE @searchstring '%'
   
OR 
   
C.ChildDescription LIKE '% ' @searchstring '%'
ORDER BY 
P.ID 
 

 

Next Steps

  • More detailed information on the CHARINDEX() function, its syntax, and usage is available here.
  • MSSQLTips.com has additional tips relying upon CHARINDEX().
  • We comb the forums at MSSQLTips.com looking for subjects to write about.  This tip came from a coworker with a specific problem.  This could have easily been about a solution to a problem you were encountering as well.


Last Update: 12/29/2008


About the author
MSSQLTips author Tim Ford
Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Sunday, April 29, 2012 - 3:30:06 AM - vahdat Read The Tip

 i have  a filed   with     value    such as   2,3,4   and        2=Bakup   3=software   4=hardware

how i can viewd value of that (2,3,4)===>(Bakup  ,software  , hardware)  in a  filed of  table/ sql


Thursday, August 30, 2012 - 9:37:44 AM - Dom Read The Tip

Vahdat:

There are two approaches.  In one approach create a Lookup Table, and then join the two.  This is actually the best approach, because the lookup table becomes a permanent document of the meaning of the values (2,3,4).  The lookup table would have the fields and values:

ID, Name
2, Backup
3, Software
4, Hardware

Then your queries would simply join to this lookup table.

A second approach is to use a CASE statement:  select case when (ID = 2) then 'Backup' else ..." and so on

 


Monday, March 11, 2013 - 5:29:18 PM - Bob Monahon Read The Tip

Hello - In the where clause, you can add a single blank prefix to the ChildDescription; then you only need one LIKE search string.

CREATE PROCEDURE uspSearchWord @searchstring VARCHAR(100AS
SELECT DISTINCT 
P.IDP.Item 
FROM ##ProductParent INNER JOIN ##ProductChild ON P.ID C.FKID 
WHERE  
 
  ' ' + C.ChildDescription LIKE '% ' @searchstring '%'
ORDER BY 
P.ID 
  



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.