How to search exact word using LIKE operator?

  • CREATE TABLE mytbl2

    (

    c1 sysname

    );

    GO

    INSERT mytbl2 VALUES ('Discount is 10-15% off');

    INSERT mytbl2 VALUES ('Discount is .10-.15 off');

    INSERT mytbl2 VALUES ('total count is 100 of total employees');

    INSERT mytbl2 VALUES ('100 is total employees count');

    INSERT mytbl2 VALUES ('100 is total employees count. here it is fine');

    INSERT mytbl2 VALUES ('count of total employees is 100');

    INSERT mytbl2 VALUES ('counting is 100');

    INSERT mytbl2 VALUES ('total counting is 100');

    GO

    Declare @name varchar(100)

    set @name = 'count'

    SELECT c1

    FROM mytbl2

    WHERE c1 LIKE '%[_]' + @name + ' %' or c1 LIKE '% ' + @name + '%' or c1 LIKE @name + ' %'

    There results are

    total count is 100 of total employees ------------------> correct

    100 is total employees count --------------------------> correct

    100 is total employees count. here it is fine -------------> correct

    count of total employees is 100 ------------------------> correct

    total counting is 100 -----------------------------------> this is incorrect

    the last row should not appear.

    Please help me searching the exact word given as parameter. it may

    Shamshad Ali

  • This is interesting one.

  • I could think of simple option here is-

    Declare @name varchar(100)

    set @name = 'count'

    SELECT c1

    FROM mytbl2

    WHERE

    c1 LIKE '% '+@name

    or

    c1 LIKE @name+' %'

    or

    c1 LIKE '% '+@name+' %'

    or

    c1 LIKE '% '+@name+'.'+'%'

    or

    c1 LIKE '% '+@name+','+'%'

    or

    c1 LIKE '% '+@name+')'+'%'

    Go on adding OR statements for each possible special characters u can think of (i.e. '.',',',')', etc)

  • Use a full-text index.

    USE Test;

    GO

    CREATE TABLE dbo.Test

    (

    row_id INTEGER IDENTITY NOT NULL,

    c1 NVARCHAR(128) NOT NULL,

    CONSTRAINT [PK dbo.Test row_id]

    PRIMARY KEY (row_id)

    );

    GO

    INSERT dbo.Test (c1) VALUES ('Discount is 10-15% off');

    INSERT dbo.Test (c1) VALUES ('Discount is .10-.15 off');

    INSERT dbo.Test (c1) VALUES ('total count is 100 of total employees');

    INSERT dbo.Test (c1) VALUES ('100 is total employees count');

    INSERT dbo.Test (c1) VALUES ('100 is total employees count. here it is fine');

    INSERT dbo.Test (c1) VALUES ('count of total employees is 100');

    INSERT dbo.Test (c1) VALUES ('counting is 100');

    INSERT dbo.Test (c1) VALUES ('total counting is 100');

    GO

    CREATE FULLTEXT CATALOG TestCatalog

    WITH ACCENT_SENSITIVITY = ON

    AUTHORIZATION dbo;

    CREATE FULLTEXT INDEX

    ON dbo.Test (c1)

    KEY INDEX [PK dbo.Test row_id]

    ON TestCatalog

    GO

    DECLARE @word NVARCHAR(100);

    SET @word = N'count';

    SELECT T.row_id,

    T.c1,

    CT.[Rank]

    FROM CONTAINSTABLE(dbo.Test, c1, @word, 10) CT

    JOIN dbo.Test T

    ON T.row_id = CT.[Key]

    ORDER BY

    CT.[Rank] DESC,

    T.row_id ASC;

    GO

  • That was interesting.

    CREATE TABLE mytbl2

    (

    c1 sysname

    );

    GO

    INSERT mytbl2 VALUES ('Discount is 10-15% off');

    INSERT mytbl2 VALUES ('Discount is .10-.15 off');

    INSERT mytbl2 VALUES ('total count is 100 of total employees');

    INSERT mytbl2 VALUES ('100 is total employees count');

    INSERT mytbl2 VALUES ('100 is total employees count. here it is fine');

    INSERT mytbl2 VALUES ('count of total employees is 100');

    INSERT mytbl2 VALUES ('counting is 100');

    INSERT mytbl2 VALUES ('total counting is 100');

    GO

    Declare @name varchar(100)

    set @name = 'count'

    SELECT c1

    FROM mytbl2

    WHERE (c1 LIKE @name + ' %') or (c1 LIKE '% ' + @name + '%') and

    (not (c1 like '%' + @name + '[a-z]%'))

    drop table mytbl2

    It works for this specific example, but I haven't tested it further. The sequence of the LIKE operators is important too.

    BrainDonor

  • Very creative.

    BrainDonor


    It works for this specific example, but I haven't tested it further.

    It is reasonable easy to find examples that break it. Nice though, as I say.

    The sequence of the LIKE operators is important too.

    It wouldn't if you use parentheses to make the logic explicit. As it stands, it depends on the precedence of OR versus AND.

    Full-text search will be much more efficient for larger searches.

  • Full-text search will be much more efficient for larger searches.

    Definitely - I'd hate to wait for this code to complete against a large table. I just saw it as an interesting puzzle to solve as I've been playing with the LIKE command recently. Very easy to drag your code to a halt with it.

    BrainDonor.

  • BrainDonor (3/19/2010)


    I just saw it as an interesting puzzle to solve as I've been playing with the LIKE command recently.

    It's even more fun to solve with a Regular Expression (available via CLR integration).

  • Paul White NZ (3/19/2010)


    BrainDonor (3/19/2010)


    I just saw it as an interesting puzzle to solve as I've been playing with the LIKE command recently.

    It's even more fun to solve with a Regular Expression (available via CLR integration).

    You're one sick puppy...:-D

    We have a developer here who doesn't understand how anyone can write code without an intimate knowledge of Regular Expressions.

    We don't let him near customers.

  • BrainDonor (3/19/2010)


    We don't let him near customers.

    Very wise! :laugh:

  • Excellent BrainDonor, really appreciable :Wow:

    Vaibhav K Tiwari
    To walk fast walk alone
    To walk far walk together

  • Hi there,

    I just changed your where clause...

    I hope this helps ^__^

    Declare @name varchar(100)

    set @name = 'count'

    SELECT c1

    FROM mytbl2

    WHERE c1 LIKE '%' + @name + '%'

    AND c1 NOT LIKE '%[A-Z]' + @name + '%'

    AND c1 NOT LIKE '%' + @name + '[A-Z]%'

    _____________________________________________
    [font="Comic Sans MS"]Quatrei Quorizawa[/font]
    :):D:P;):w00t::cool::hehe:
    MABUHAY PHILIPPINES!

    "Press any key...
    Where the heck is the any key?
    hmmm... Let's see... there's ESC, CTRL, Page Up...
    but no any key"
    - Homer Simpson
  • btw guys, how do you know if someone replied to you in a forum or posted something in the same forum you posted? I mean do you have any notifications like facebook here or do you have to find that post again? hehehe!

    🙂

    _____________________________________________
    [font="Comic Sans MS"]Quatrei Quorizawa[/font]
    :):D:P;):w00t::cool::hehe:
    MABUHAY PHILIPPINES!

    "Press any key...
    Where the heck is the any key?
    hmmm... Let's see... there's ESC, CTRL, Page Up...
    but no any key"
    - Homer Simpson
  • how do you delete a post?

    _____________________________________________
    [font="Comic Sans MS"]Quatrei Quorizawa[/font]
    :):D:P;):w00t::cool::hehe:
    MABUHAY PHILIPPINES!

    "Press any key...
    Where the heck is the any key?
    hmmm... Let's see... there's ESC, CTRL, Page Up...
    but no any key"
    - Homer Simpson
  • You should ask such questions in the forum of site

    http://www.sqlservercentral.com/Forums/Forum433-1.aspx

    Vaibhav K Tiwari
    To walk fast walk alone
    To walk far walk together

Viewing 15 posts - 1 through 15 (of 18 total)

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