TSQL LIKE search

I currently have a query that uses LIKE for wildcard searching e.g.

select * from title where job LIKE %music%

Open in new window

and this will bring back results 'music teacher', 'teacher music' etc. The issue is when I search for 'music teacher' or 'good music teacher' only one result is returned or none whereas I would like for 'music teacher' and 'teacher music' to be returned. Is there a TSQL function that allows me to split the keywords in the string and search so that for example 'very good music teacher' still brings the results?
David CIT Projects ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Forgot the single quote marks
select * from title where job LIKE '%music%'

Open in new window

Also, not quite sure what you are asking us, so a data mockup with expected results would help.
Vitor MontalvãoMSSQL Senior EngineerCommented:
What you need is FULL TEXT SEARCH feature.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David CIT Projects ManagerAuthor Commented:
Hi, thank you here is what I mean
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

David CIT Projects ManagerAuthor Commented:
Ok I am doing some reading on FULL TEXT now
Jim HornMicrosoft SQL Server Data DudeCommented:
The code posted above will return all four rows, as they all have '%music%' in them.
Also, all three of your LIKE string searches have 'music' in them, so that's redundant.
So .. let us know how that does not meet your requirements, as it's still not clear.
Brian CroweDatabase AdministratorCommented:
FULL TEXT is definitely the way to go.  You will need to parse your incoming string and search each word separately then sum their weights returned by FREETEXTTABLE or CONTAINSTABLE.  I have some sample code I can share if you decide to go that route.
David CIT Projects ManagerAuthor Commented:
Hey, thank you all. This is where I am now. Learnt so much in a short period of time! Ideally when I search for "music teacher" I am hoping to see all the 6 rows or at least "Teacher Music" and "Music" should be included. It looks like its looking for a phrase at the moment so how do I split/separate the phrase so that it searches accordingly.

FTS shot
David CIT Projects ManagerAuthor Commented:
@Jim Horn thank you - the query with LIKE '%music%' returns all rows but '%music teacher%' only returns some excluding 'musician', 'teacher music' which is not what I need.
David CIT Projects ManagerAuthor Commented:
I think I got it now

SELECT [id],[title]
FROM [dbo].[fts]
WHERE CONTAINS((title),'ISABOUT ("music*","teacher")')

Open in new window

Brian CroweDatabase AdministratorCommented:
Below is some code taking advantage of FULL-TEXT INDEXING.  It's pretty easy to set up.

	@Text			VARCHAR(255),
	@UserID			INT = 0
	ImageID			INT,
	INSERT INTO @FTResult (ImageID, [RANK], [Source])
	SELECT ImgK.ImageID, FT.[RANK] * ImgK.[Weight], 'Keyword'
	FROM FREETEXTTABLE(Kepler.dbo.Keywords, Keyword, @Text) AS FT
	INNER JOIN dbo.Keywords AS K
		ON FT.[KEY] = K.KeywordID
	INNER JOIN dbo.Images_Keywords AS ImgK
		ON K.KeywordID = ImgK.KeywordID      


Open in new window


@SearchTerms is a table of search terms created using a simple Split function which I can also supply if needed

@ColumnWeight probably won't apply in your case and you can pull it out.
		SELECT FT.ImageID, SUM(FT.[RANK] * CW.[Weight]) AS [Weight]
		FROM @SearchTerms AS ST
		CROSS APPLY dbo.ImageFTSearch (ST.Value, @UserID) AS FT
		INNER JOIN @ColumnWeight AS CW
			ON FT.[Source] = CW.[Column]

Open in new window

David CIT Projects ManagerAuthor Commented:
Hey Brian, thanks for the response - I am a bit confused with your solution which I'm sure works but I cannot understand it.

Please could you help me with this?

DECLARE @keywords nvarchar(50)
SET @keywords='music teacher'

SELECT [id],[title]
FROM [dbo].[fts]
WHERE CONTAINS((title),'ISABOUT ("music","teacher")')

Open in new window

What do I do to the @keywords to produce "music", "teacher" so that I can replace {'IS ABOUT ("music","teacher")'} with {'IS ABOUT (@keywords)'}
Brian CroweDatabase AdministratorCommented:
I'll back up a little bit and explain a little better since I kind of just dumped that code on you and it doesn't exactly match up with your needs.  My example is searching a Keyword table that contains just an IDENTITY value and a full-text indexed Keyword column.
FREETEXTTABLE(Kepler.dbo.Keywords /*table to be searched*/, Keyword /*full-text indexed column*/, @Text /*word to search for*/)

Open in new window

returns a table consisting of the primary key value from the table and a ranking value based on how well it matches the table value (FREETEXTTABLE automatically handles stemming like pluralization and case i.e. a search for "run" will match on "ran", "running", etc.).  The rest of the UDF is joining the Keyword back to the object I really care about which is an Image table because those are the values I really care about for my purposes.

The Usage part of the code is simply calling the UDF for each entry in a table variable @SearchTerms that has already been split out from the parameter that might have a value of "blue christmas card"  In this example @SearchTerms would consist of 3 rows, one for each word.  The UDF is called for each word and then SUMs up the returned ranking values from the FREETEXTTABLE response.  This effectively performs an OR search such that if there was an image that had a keyword match for any of those values it would be returned but an image that had all 3 would be listed higher based on the sum.  The ColumnWeight stuff can be stripped out because that is dealing with a separate search of the ImageName that we wanted to weight higher than the keyword and doesn't apply in your case.  I removed that code from the UDF before I posted it because I thought it would just confuse things.  It should be stripped out of the usage code sample as well probably.
		SELECT FT.ImageID, SUM(FT.[RANK]) AS [Weight]
		FROM @SearchTerms AS ST
		CROSS APPLY dbo.ImageFTSearch (ST.Value, @UserID) AS FT
                ORDER BY SUM(FT.[RANK] DESC

Open in new window

Hopefully this helps a little.
David CIT Projects ManagerAuthor Commented:
Thank you all, I managed to do it this way;

DECLARE @SQL nvarchar(4000)
DECLARE @SELECT nvarchar(4000)
DECLARE @SEARCH nvarchar(4000)
DECLARE @KeywordSearch nvarchar(500)

SET @KeywordSearch='"music","teacher"'
SET @SELECT =   'SELECT [id],[title] FROM [dbo].[title] WHERE '
SET @SEARCH =   ' CONTAINS((title), ''ISABOUT ('+@KeywordSearch+')' + ''''  + ')'
EXEC sp_executesql @SQL

Open in new window

David CIT Projects ManagerAuthor Commented:
Moderator how do I reassign points please? I forgot to give Brian Crowe some!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.