Only match complete words. - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Only match complete words.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-30 : 08:29:49

Sam writes "Hi,

I need to search for complete words in SQL server without a full text index. I can get it to work but not for words that are the first or last in a record. For the life of me i cant think of a workaround. If you could suggest a solution i would be very gratefull. So far i use something like:

Dim strBadCharsLeft ' These are the characters that could possible be prefixing a word.
Dim strBadCharsRight ' These are the characters that could possible be trailing a word.
strBadCharsLeft = "[.,/?!@#$%^&*() ]"
strBadCharsRight = "[.,/?!@#$%^&*()s ]"
Dim strLogic
strLogic = "OR"

strSQL = strSQL & " (ProRecruit_Vacancy.Vacancy_Title LIKE '%" & strBadCharsLeft & strKeywords2 & strBadCharsRight & "%') " & strLogic & " (ProRecruit_Vacancy.Vacancy_Description LIKE '%" & strBadCharsLeft & " " & strKeywords2 & " " & strBadCharsRight & "%') " & strLogic
strSQL = strSQL & " (ProRecruit_Vacancy.Vacancy_Title LIKE '%" & strKeywords2 & strBadCharsRight & "%') " & strLogic & " (ProRecruit_Vacancy.Vacancy_Description LIKE '%" & strKeywords2 & strBadCharsRight & "%') " & strLogic
strSQL = strSQL & " (ProRecruit_Vacancy.Vacancy_Title LIKE '%" & strBadCharsLeft & strKeywords2 & "%') " & strLogic & " (ProRecruit_Vacancy.Vacancy_Description LIKE '%" & strBadCharsLeft & strKeywords2 & "%') " & strLogic

'---------------------

Thanks,
Sam."

SQuirreL
Starting Member

9 Posts

Posted - 2002-09-01 : 23:47:09

A little twist which might help you (I first noticed it while reading this article by Merkin). The key is to recognize that you can concatenate text data to any varchar field in an SQL statement--not just to keyword strings. So, as long as your columns Vacancy_Title and Vacancy_Description are of data type varchar (and not text) then this should work:

Dim strBadCharsLeft ' These are the characters that could possible be prefixing a word.
Dim strBadCharsRight ' These are the characters that could possible be trailing a word.
strBadCharsLeft = "[.,/?!@#$%^&*() ]"
strBadCharsRight = "[.,/?!@#$%^&*()s ]"
Dim strLogic
strLogic = "OR"

strSQL = strSQL & " (' ' + ProRecruit_Vacancy.Vacancy_Title + ' ' LIKE '% " & strBadCharsLeft & strKeywords2 & strBadCharsRight & " %') " & strLogic & " (' ' + ProRecruit_Vacancy.Vacancy_Description + ' ' LIKE '% " & strBadCharsLeft & " " & strKeywords2 & " " & strBadCharsRight & " %') " & strLogic
strSQL = strSQL & " (' ' + ProRecruit_Vacancy.Vacancy_Title + ' ' LIKE '% " & strKeywords2 & strBadCharsRight & " %') " & strLogic & " (' ' + ProRecruit_Vacancy.Vacancy_Description + ' ' LIKE '% " & strKeywords2 & strBadCharsRight & " %') " & strLogic
strSQL = strSQL & " (' ' + ProRecruit_Vacancy.Vacancy_Title + ' ' LIKE '% " & strBadCharsLeft & strKeywords2 & " %') " & strLogic & " (' ' + ProRecruit_Vacancy.Vacancy_Description + ' ' LIKE '% " & strBadCharsLeft & strKeywords2 & " %') " & strLogic

'--------------------

My changes (in bold) essentially concatenate a space (' ') to the beginning and end of the table columns, and to the beginning and end of the keyword string (inside the wildcard character). So instead of comparing ('Vacancy_Title Value' LIKE '%keywords%') it compares (' Vacancy_Title Value' LIKE '% keywords %'). This way it searches for whole words using a space as the word boundary (and you add an extra space to the start and end of the text in the table cell, so that the first and last words will match as well).

Unfortunately (as I recall from a month or so ago when I was trying to make this work) SQL Server will not allow you to concatenate text (i.e. the spaces) to a field of type text or ntext, so if either field is that datatype, you have to either CAST the text column as varchar(8000) before concatenating it with the spaces (and lose the ability to search any text beyond the 8000th character) or find another solution (which I haven't been able to do).

I hope this makes sense. Reply if it doesn't and I will try to explain better.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources