searching for whole words in text fields

Question

searching for whole words in text fields

geoff hopkins

Ten Centuries

Points: 1147
More actions
November 1, 2006 at 9:12 am

#116169

created a user defined function in tsql that searches for strings within a text field, using the CHARINDEX and SUBSTRING functions..... the problem I have found is this ....... if the string is 'or' and the text field is 'forever young' this returns true for the search of 'or'....... i was wondering if there is a better way to do text searching .... i have thought of modifying so that it searchs for ' or' or if the string starts with 'or' but was hoping there was a more elequant way of achieving this

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 1

taking your target str 'or' as an example, what u r looking here is the occurrences of the string in the form of a 'stand-alone' word i.e. speaking in regular expressions language :

\bor\b

where \b denotes 'word boundary'

Unfortunately SQL Server 2000 (if u use it) does not have decent regex support , so it'll be tough to emulate the pattern by tangling a bunch of CHARINDEX and SUBSTRING statements. In SQL 2005 u could write a CLR function that would call .NET REgexObject and find all the occurrences of word 'or' in a fld.

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 2

In your search function add additional logic to check the word before and after and check if its blank space.

Lowell SSC Guru Points: 323518 More actions · Answer 3

Sergei is right, but there is some extended stored procedures you can put on your sql 2000 server in order to use regular expressions in SQL 2000:

read this for all the details: http://www.codeproject.com/managedcpp/xpregex.asp

i've used them for several years; very helpful for data cleanups.

something similar, or maybe even in the same thing, is part of the SQL DBA Toolkit:

http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart2.asp

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 4

waht if 'or' is at the start or end of a tested str? then u'll need more 'additional logic' I guess.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Since none of the methods are likely to use an available index when used in a WHERE clause, try this one...

--=============================================================

-- This section of code is just setup for a test and is

-- not part of the solution.

--=============================================================

--===== If the demo table exists, drop it

IF OBJECT_ID('TempDB..#DemoWords') IS NOT NULL

DROP TABLE #DemoWords

--===== Create the demo table

CREATE TABLE #DemoWords

(

RowNum INT IDENTITY(1,1) PRIMARY KEY,

SomeString VARCHAR(200)

)

--===== Populate the demo table with words

INSERT INTO #DemoWords (SomeString)

SELECT 'Forever Young' UNION ALL

SELECT 'Or what?' UNION ALL

SELECT 'You can stay or' UNION ALL

SELECT 'You can have this or that' UNION ALL

SELECT 'Oregon is a state' UNION ALL

SELECT 'He is a tutor'

--=============================================================

-- This section of the code IS part of the solution.

--=============================================================

--===== This could be a parameter in a stored proc...

DECLARE @Word2Find VARCHAR(22)

SET @Word2Find = 'or'

--===== Find everything that has the WHOLE word to find

SELECT *

FROM #DemoWords

WHERE ' '+SomeString+' ' LIKE '% '+@Word2Find+' %'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Jeff, make @Word2Find = 'what' and run it.

It cannot find "what" because there is no space after it.

Same problem with dots, commas, etc.

_____________
Code for TallyGenerator

geoff hopkins Ten Centuries Points: 1147 More actions · Answer 7

thanks everyone for the help went with the regexpr stuff at http://www.codeproject.com/managedcpp/xpregex.asp was pretty impressed

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Yep... you're right... forgot about all that punctuation stuff. Thanks, Serqiy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)