May 9, 2012 at 5:49 am
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)
May 9, 2012 at 6:12 am
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
May 9, 2012 at 6:16 am
This is not working
Your query does not gives the result on the following inputs
Country My
My India
Country India
May 9, 2012 at 6:38 am
yes, i see that....
you would have to implement Full Text Search and use the CONTAINS statement i think.
May 9, 2012 at 6:53 am
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
May 9, 2012 at 7:17 am
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