search problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

search problem

Author

Topic

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-10-28 : 06:17:11

hello,
I have a string say 'test test1 test2 test3'
now I have to select only those rows where one of the field in the table matches with any two words (say test and test2 )in the string.

any ideas?
regards,
harshal.

The Judgement of the Judge is as good as the Judge.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-10-28 : 07:21:09

set nocount on
create table #q( id int identity(0,1),string varchar(100))

insert into #q select 'test0 test1 test4 test5'
insert into #q select 'test test1 test2 test3'

declare @searchstring varchar(100),
@strsql varchar(1000),
@firststring int,
@thisstring varchar(30)

set @strsql = ''
set @searchstring = 'test2 test3'
set @firststring = 0

while len(@searchstring) > 0
begin

declare @pos int
set @pos = 1

select @pos = charindex(' ',@searchstring)

if @pos <= 0
begin
set @thisstring = rtrim(ltrim(@searchstring))
if len(@thisstring) > 0
begin
if @firststring = 0
begin
select @strsql = 'select * from #q where string like ''%' + @thisstring + '%'''
end
else
begin
select @strsql = @strsql + ' OR string like ''%' + @thisstring + '%'''
end

set @firststring = 1
end

break
end

set @thisstring = rtrim(ltrim(substring(@searchstring,1,@pos))) --,@searchstring
select @searchstring = substring(@searchstring,@pos+1,len(@searchstring)-@pos)
if len(@thisstring) > 0
begin
if @firststring = 0
begin
select @strsql = 'select * from #q where string like ''%' + @thisstring + '%'''
end
else
begin
select @strsql = @strsql + ' OR string like ''%' + @thisstring + '%'''
end

set @firststring = 1
end

end

select @strsql
exec (@strsql)

drop table #q

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-10-28 : 07:37:44

quote:
Originally posted by ehorn

select * from #q where string like '%test2%' and id in (select id from #q where string like '%test%')

here you have hard coded the strings ,but I do not know what the words are ,I just have to match any two words in the string with those in the column.

The Judgement of the Judge is as good as the Judge.

dsdeming

479 Posts

Posted - 2003-10-28 : 08:30:19

This method will work, but it depends on the use of a delimiter:

SET NOCOUNT ON
CREATE TABLE #q( id int IDENTITY(0,1), string varchar(100))

INSERT INTO #q SELECT 'test0 test1 test4 test5'
INSERT INTO #q SELECT 'test test1 test2 test3'

SELECT * FROM #q

DECLARE @search1 varchar( 100 ), @search2 varchar( 100 )
SET @search1 = 'test3'
SET @search2 = 'test2'

SELECT a.*
FROM ( SELECT id, ' ' + string + ' ' AS string FROM #q ) a
WHERE CHARINDEX( ' ' + @search1 + ' ', string ) > 0
AND CHARINDEX( ' ' + @search2 + ' ', string ) > 0

DROP TABLE #q

Notice that the FROM clause uses ' ' + string + ' ' to ensure that the string has the required delimiters. This approach is required because in the example we're looking for both test and test2, and using test% is not precise enough to differentiate between the values in your data. Using CHARINDEX with leading and trailing delimiters ensures that we can search for only exact matches.

Dennis

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-10-28 : 10:21:03

quote:
Originally posted by harshal_in

quote:
Originally posted by ehorn

select * from #q where string like '%test2%' and id in (select id from #q where string like '%test%')

here you have hard coded the strings ,but I do not know what the words are ,I just have to match any two words in the string with those in the column.

The Judgement of the Judge is as good as the Judge.

I updated my original post to parse the incoming string and build a search string. This will need work to accomodate all possibilities. Hope this helps...

MakeYourDaddyProud

184 Posts

Posted - 2003-10-28 : 10:47:50

Fill in the start and end positions of the tokens (substring) and you are looking at a similar version to:-

create table #q (id int primary key identity, string varchar(100))

declare @p varchar(100)
set @p = '%test%test1%test2%test3%'

select *
from #q qa
left join
(select id, 1 as found
from #q
where string like substring(@p,1,7)
) as p1
on p1.id = qa.id
left join
(select id, 1 as found
from #q
where string like substring(@p,8,7)
) as p2
on p2.id = qa.id
left join
(select id, 1 as found
from #q
where string like substring(@p,15,7)
) as p3
on p3.id = qa.id
left join
(select id, 1 as found
from #q
where string like substring(@p,22,7)
) as p4
on p4.id = qa.id
where ( isnull(p1.found,1) +
isnull(p2.found,1) +
isnull(p3.found,1) +
isnull(p4.found,1) = 2 )

drop table #q

Daniel Small MIAP
www.danielsmall.com IT Factoring

MakeYourDaddyProud

184 Posts

Posted - 2003-10-28 : 10:52:41

Oversight.

isnull(p(n).found,1) should be isnull(p(n).found,0)

Daniel Small MIAP
www.danielsmall.com IT Factoring

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-10-29 : 04:32:20

Thanks everyone i am trying them out.

The Judgement of the Judge is as good as the Judge.

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-10-29 : 05:10:43

quote:
Originally posted by ehorn

set nocount on
create table #q( id int identity(0,1),string varchar(100))

insert into #q select 'test0 test1 test4 test5'
insert into #q select 'test test1 test2 test3'

declare @searchstring varchar(100),
@strsql varchar(1000),
@firststring int,
@thisstring varchar(30)

set @strsql = ''
set @searchstring = 'test test2'
set @firststring = 0

while len(@searchstring) > 0
begin

declare @pos int
set @pos = 1

select @pos = charindex(' ',@searchstring)

if @pos <= 0
begin
set @thisstring = rtrim(ltrim(@searchstring))
if len(@thisstring) > 0
begin
if @firststring = 0
begin
select @strsql = 'select * from #q where string like ''%' + @thisstring + '%'''
end
else
begin
select @strsql = @strsql + ' and string like ''%' + @thisstring + '%'''
end

set @firststring = 1
end

break
end

set @thisstring = rtrim(ltrim(substring(@searchstring,1,@pos))) --,@searchstring
select @searchstring = substring(@searchstring,@pos+1,len(@searchstring)-@pos)
if len(@thisstring) > 0
begin
if @firststring = 0
begin
select @strsql = 'select * from #q where string like ''%' + @thisstring + '%'''
end
else
begin
select @strsql = @strsql + ' and string like ''%' + @thisstring + '%'''
end

set @firststring = 1
end

end

select @strsql
exec (@strsql)

drop table #q

I tried this,doesn't seem to work .actually the query @strsql at the end becomes :
select * from table where columnname= this and columnname =this and columnname=this and columnname=this .....

where as I am trying to select those rows from the table where any of the two words from the search string match.

The Judgement of the Judge is as good as the Judge.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-10-29 : 07:24:57

quote:
Originally posted by harshal_in

[quote]Originally posted by ehorn

I tried this,doesn't seem to work .actually the query @strsql at the end becomes :
select * from table where columnname= this and columnname =this and columnname=this and columnname=this .....

where as I am trying to select those rows from the table where any of the two words from the search string match.

Perhaps replacing logical AND with logical OR in D-SQL.

MakeYourDaddyProud

184 Posts

Posted - 2003-10-29 : 08:35:07

Provided the token string has a finite number of strings to search, the only way it can be done in 1 sql pass (no proc code) would be to left join with a like for each string returning a '1' as a contrived column where the like predicate matched and then filter the rows by checking isnull on the contrived column.

quote:

where
(isnull(leftjoin1.found,1) +
isnull(leftjoin2.found,1) +
isnull(leftjoin3.found,1) +
isnull(leftjoin4.found,1) = 2 )

Honest!

Daniel Small MIAP
www.danielsmall.com IT Factoring

cmf
Starting Member

3 Posts

Posted - 2003-10-29 : 11:50:15

Hi...

Couldn't you use the PATINDEX function - or am I way off on what it is you're trying to accomplish

SET NOCOUNT ON
CREATE TABLE #q( id int IDENTITY(0,1), string varchar(100))

INSERT INTO #q SELECT 'test0 test1 test4 test5'
INSERT INTO #q SELECT 'test test1 test2 test3'
INSERT INTO #q SELECT 'test0 test1 test2 test3'

DECLARE @search1 varchar( 100 ), @search2 varchar( 100 )
SET @search1 = 'test1'
SET @search2 = 'test0'

SELECT *
FROM #q
WHERE PATINDEX('%'+ @search1 +'%', string) >=1
ANDPATINDEX('%' + @search2 + '%', string) >=1

DROP TABLE #q

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-30 : 00:56:44

Assuming you've parsed your input string into four variables like @test1, @test2...

If you are going to receive a variable number of words, you might want to consider putting a maximum limit on the numbers of words, and provide defaults which are unlikely to be found in the table.


SELECT ID FROM
(
  SELECT ID FROM Table WHERE ColumnA LIKE '%' + @test1 + '%'
  UNION ALL
  SELECT ID FROM Table WHERE ColumnA LIKE '%' + @test2 + '%'
  UNION ALL
  SELECT ID FROM Table WHERE ColumnA LIKE '%' + @test3 + '%'
  UNION ALL
  SELECT ID FROM Table WHERE ColumnA LIKE '%' + @test4 + '%'
) SubQuery1
GROUP BY ID
HAVING COUNT(*) > 1

Owais

Make it idiot proof and someone will make a better idiot

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-10-30 : 08:04:55

quote:
Originally posted by mohdowais

Assuming you've parsed your input string into four variables like @test1, @test2...

If you are going to receive a variable number of words, you might want to consider putting a maximum limit on the numbers of words, and provide defaults which are unlikely to be found in the table.

SELECT ID FROM
(
SELECT ID FROM Table WHERE ColumnA LIKE '%' + @test1 + '%'
UNION ALL
SELECT ID FROM Table WHERE ColumnA LIKE '%' + @test2 + '%'
UNION ALL
SELECT ID FROM Table WHERE ColumnA LIKE '%' + @test3 + '%'
UNION ALL
SELECT ID FROM Table WHERE ColumnA LIKE '%' + @test4 + '%'
) SubQuery1
GROUP BY ID
HAVING COUNT(*) > 1

Owais

Make it idiot proof and someone will make a better idiot

I Think this is just what I was looking for.
Thanks a lot.
regards,
harshal.

The Judgement of the Judge is as good as the Judge.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources