How to find a specific word in a SQL table

5 pts.
Tags:
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

SELECT *
FROM YourTable
WHERE Column like 'bob%'
or Column like 'bob%'
or Column like '% bob %'

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Kccrosser
    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.
    3,830 pointsBadges:
    report
  • TomLiotta
    Since some parts of an answer might change, what database product is being used? Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following