Search Patteren

  • I have column in my table with type varchar. My application having a search crieria and its looking this table.

    Suppose column contain follwoing data

    India is My country.

    When a user search by giving following inputs

    India

    India Is

    India My

    Is Cuuntry

    Country

    Cuuntry My

    My India

    India Country

    All these inputs have the same result.(India is My country)

    How to check this search patteren in sqlserver (using Like keyword)

  • you could something like this;

    declare @MyTable table (MyColumn varchar(255))

    insert into @MyTable

    select 'India is My country'

    declare @variable varchar(255)

    set @variable = 'India'

    select MyColumn from @MyTable

    where MyColumn like '%'+@variable+'%'

    and have the application set @variable

  • This is not working

    Your query does not gives the result on the following inputs

    Country My

    My India

    Country India

  • yes, i see that....

    you would have to implement Full Text Search and use the CONTAINS statement i think.

  • select MyColumn from @MyTable

    where MyColumn like '%'+@variable+'%'

    Set up full text search on mytable/mycolumn and change your select from a like to a contains.

    Using a wildcard at the front of the variable will perform a table scan, and if the table is large and/or there are multiple users then you will see your wait times increase with a like comparison.

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

  • here is a working examples of Full Text Search on a Test database;

    WARNING, if you have a database called Test, do not run the statement.....

    USE [master]

    GO

    IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Test')

    ALTER DATABASE Test

    SET SINGLE_USER

    WITH ROLLBACK IMMEDIATE

    USE [master]

    GO

    IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Test')

    DROP DATABASE [Test]

    GO

    CREATE DATABASE [Test] ON PRIMARY

    ( NAME = N'Test', FILENAME = N'C:\Test.mdf' , SIZE = 4048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

    LOG ON

    ( NAME = N'Test_log', FILENAME = N'C:\Test_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

    GO

    IF OBJECT_ID('Test.dbo.MyTable') IS NOT NULL

    DROP TABLE Test.dbo.MyTable

    create table Test.dbo.MyTable (id int NOT NULL IDENTITY (1, 1),[MyColumn] varchar(128),

    CONSTRAINT [PK_ResultsTable] PRIMARY KEY CLUSTERED ([id] ASC))

    Use Test

    go

    EXEC sp_fulltext_database 'enable'

    CREATE FULLTEXT CATALOG MyTableCatalog AS DEFAULT;

    EXEC sp_fulltext_table 'Test.dbo.MyTable', 'create', 'MyTableCatalog', 'PK_ResultsTable'

    EXEC sp_fulltext_column 'Test.dbo.MyTable', 'MyColumn', 'add'

    EXEC sp_fulltext_table 'Test.dbo.MyTable','activate'

    EXEC sp_fulltext_catalog 'MyTableCatalog', 'start_full'

    insert into Test.dbo.MyTable

    select 'India is My country'

    waitfor delay '00:00:05' -- have to wait for the catalog to get populated.

    USE Test;

    GO

    DECLARE @SearchWord nvarchar(30)

    SET @SearchWord = N'"Country My"'

    SELECT MyColumn

    FROM MyTable

    WHERE CONTAINS(MyColumn, @SearchWord)

Viewing 6 posts - 1 through 5 (of 5 total)

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