Post reply

searching for whole words in text fields

  • 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

  • November 1, 2006 at 10:32 am

    #669267

    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.

  • November 1, 2006 at 11:34 am

    #669279

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

     

  • November 1, 2006 at 11:35 am

    #669281

    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://qa.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!

  • November 1, 2006 at 12:26 pm

    #669309

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

  • November 1, 2006 at 6:53 pm

    #669378

    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)
    Intro to Tally Tables and Functions

  • November 1, 2006 at 7:55 pm

    #669381

    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

  • November 2, 2006 at 3:34 am

    #669409

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

  • November 2, 2006 at 6:09 am

    #669446

    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)
    Intro to Tally Tables and Functions

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

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