text boolean search with multiple phrases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

text boolean search with multiple phrases

Author

Topic

lleemon
Starting Member

24 Posts

Posted - 2012-01-12 : 12:45:23

I have a table called 'Job' which has a field called 'Job_Keywords'. I have process that runs and loops through each of the 'Jobs' records. A new addition I would like to add is if 'Job_Keywords' has the value of 'Javascript OR SSL' it would basically so something like the following:

SELECT *
FROM Candidates c
WHERE Candidates_Resume like '%Javascript%' OR Candidates_Resume like '%SSL%'

The issue is I can't hard code it because someone could have a lot of possible formats in the Job table field Job_Keywords:
Javascript
Javascript OR SSL
Javascript AND SSL
Javascript AND (ASP OR ASPX)
(Javascript OR jquery) OR SSL OR (ASP AND ASPX)
... plus many more possibilities.

Is there a standard on how to create a search like this?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-12 : 12:57:29

so in case Javascript AND (ASP OR ASPX)

you want query like


SELECT * 
FROM Candidates c
WHERE Candidates_Resume like '%Javascript%' OR Candidates_Resume like '%ASP%' OR Candidates_Resume like '%ASPX%'

??

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lleemon
Starting Member

24 Posts

Posted - 2012-01-12 : 13:01:45

Well, I think this 'Javascript AND (ASP OR ASPX)' would equal this:

SELECT *
FROM Candidates
WHERE Candidates_Resume like '%Javascript%' AND (Candidates_Resume like '%ASP%' OR Candidates_Resume like '%ASPX%')

Is there a way to put the WHERE condition in a variable and call like so:

DECLARE @SEARCH_COND varchar(2000)
--I am hard coding but would need a function to take my initial text and create the following
SET @SEARCH_COND = "Candidates_Resume like '%Javascript%' AND (Candidates_Resume like '%ASP%' OR Candidates_Resume like '%ASPX%')"
SELECT *
FROM Candidates
WHERE @SEARCH_COND

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-12 : 13:09:23

looks like what you need is a full text index search.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lleemon
Starting Member

24 Posts

Posted - 2012-01-12 : 15:19:55

Wow, that's nice. Thanks. Works nicely.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-13 : 03:58:40

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources