Home > IT Answers > SQL Server > How to find a specific word in a SQL table
Boomer1
5 pts.
 How to find a specific word in a SQL table
SELECT statement, SQL, SQL Query, WHERE statement
How do you write a SQL query to find a specific word in a SQL column. Example: I want to show all records that have the word Bob in it but do not want to include records that are Bobby or Bobble etc., only the exact word Bob. Hope that makes sense.

Software/Hardware used:
ASKED: January 29, 2009  3:13 AM
UPDATED: April 18, 2013  8:42 PM
RELATED QUESTIONS
Answer Wiki:
You would have to search for the word with spaces on one side, as well as on other sides. As a second option you could use full text indexing to search for the word bob, but if you are searching for words such as "and" you'll have to do some additional configuration to be able to search for "noise" words. 
<pre>SELECT *
FROM YourTable
WHERE Column like 'bob%'
or Column like 'bob%'
or Column like '% bob %'</pre>
Last Wiki Answer Submitted:  April 18, 2013  8:42 pm  by  Michael Tidmarsh   11,410 pts.
All Answer Wiki Contributors:  Michael Tidmarsh   11,410 pts. , Denny Cherry   64,520 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

I would suggest using a function to do the “hard” logic.

Use a simple query to find the records containing the substring of interest, then apply the function to those to get just the ones where the substring is a “word”.

declare sSubString varchar(255);
declare sWildCardString varchar(255);

set sSubString = 'bob';
set sWildCardString = '%' + sSubString + '%';

SELECT *
FROM YourTable
WHERE Column like sWildCardString
and MyFunction(sSubString, Column) = 1

The logic for the function is actually pretty simple:

function MyFunction (
psSubString varchar(255),
psSourceString varchar(max))
return integer
begin
	declare i integer;
	declare iFound integer;
	set i = 1;
	set iFound = 0;
	while i > 0 and iFound = 0
	begin
		set i = CHARINDEX(psSubString, sSourceString, i);
		if i > 0
		begin
			set iFound = 1;
			if i > 1 and CHARINDEX(substring(sSourceString,i,1),'abcdefghijklmnopqrstuvwxyz') > 0
			set iFound = 0;
			if i < length(sSourceString) and CHARINDEX(substring(sSourceString,i+length(sSubString),1),'abcdefghijklmnopqrstuvwxyz') > 0
			set iFound = 0;
		end;
		set i = i + 1;
	end;
	return iFound;
end;

The function finds EACH occurrence of the substring (might be more than one), then checks to make sure that the occurrence is not preceded or followed by an alpha character, which will handle any typical embedded text substrings.

Kccrosser
 3,830 pts.
 

Since some parts of an answer might change, what database product is being used?

Tom

TomLiotta
 108,330 pts.