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?
LVL 1
takwirirarIT Projects ManagerAsked:
Who is Participating?
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 Developer, Architect, and AuthorCommented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
What you need is FULL TEXT SEARCH feature.
0

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
takwirirarIT Projects ManagerAuthor Commented:
Hi, thank you here is what I mean
string.png
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

takwirirarIT Projects ManagerAuthor Commented:
Ok I am doing some reading on FULL TEXT now
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
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.
0
takwirirarIT 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
0
takwirirarIT 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.
0
takwirirarIT 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

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

CREATE FUNCTION dbo.ImageFTSearch
(
	@Text			VARCHAR(255),
	@UserID			INT = 0
)
RETURNS @FTResult TABLE
(
	ImageID			INT,
	[RANK]			INT
)
AS
BEGIN
	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      

	RETURN;
END

Open in new window


Usage:

@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]
		GROUP BY FT.ImageID

Open in new window

0
takwirirarIT 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)'}
0
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
		GROUP BY FT.ImageID
                ORDER BY SUM(FT.[RANK] DESC

Open in new window

Hopefully this helps a little.
0
takwirirarIT 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+')' + ''''  + ')'
SET @SQL    =   @SELECT + @SEARCH
PRINT @SQL
EXEC sp_executesql @SQL

Open in new window

0
takwirirarIT Projects ManagerAuthor Commented:
Moderator how do I reassign points please? I forgot to give Brian Crowe some!
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.