searching - 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)

searching

Author

Topic

m_saad
Starting Member

22 Posts

Posted - 2005-01-15 : 07:01:11

Hello All,
I have a certain problem regarding searching technique. Problem is in one of the table I have the following data: for example

Plates
Expensive Plates
Templates

And the keyword I need to search is “plate”. Now I need to write a query which will return.

Plate
Expensive Plates

Since both have the matching pattern at the start of any word in the string, and the result should not include Templates because the keyword “plate” doesn’t come at the beginning of the word. This can be easily done with regular expressions but is there any way to accomplish this task in SQL?

I hope I have explained it well enough

Thanking you all

Saad

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-15 : 07:15:15

[code]
SET NOCOUNT ON

--create tally table
Create Table numbers( n int primary key)

--Generate some data for it
Declare @i int
Set @i = 0
while @i <= 1000
begin
insert numbers Values (@i)
Set @i = @i + 1
end

--test data
create table main (main_name varchar(20))

insert into main
select 'Plates' union all
select 'Expensive Plates' union all
select 'Templates'

declare @SearchWord varchar(20)
set @SearchWord = 'Plates'

--find matches
select distinct main_name
from
(
select
distinct main_name,
NullIf(SubString(' ' + main_name + ' ' , n , CharIndex(' ' , ' ' + main_name + ' ' , n) - n) , '') AS Word
from
numbers, main
where
n <= Len(' ' + main_name + ' ') AND SubString(' ' + main_name + ' ' , n - 1, 1) = ' '
and NullIf(SubString(' ' + main_name + ' ' , n , CharIndex(' ' , ' ' + main_name + ' ' , n) - n) , '') IS NOT NULL
) d
where @SearchWord = word

drop table main[/code]

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-15 : 09:37:12

[code]declare @searchword varchar(20)
set @searchword = 'plate'
select * from
( select 'Plates' as word union all
select 'Expensive Plates' union all
select 'Templates' ) w
where
patindex(@searchword + '%',word) = 1
or
patindex('%[ ]' + @searchword + '%',word) > 1[/code]

rockmoose

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-15 : 09:59:26

Note to self...

Never post sql solution in morning w/o having at least 2 cups of coffee.

Nice Rockmoose :)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-15 : 10:12:27

I particularily like your use of the NULLIF function though.
I rarely get to see it in actual use

rockmoose

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-15 : 10:15:07

I swiped the entire statement from Robs csv article :)

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-15 : 11:44:01

That will also give a result for platelets and platella - but maybe that's what you want.
could be

where ' ' + word like '% ' + @searchword + '%'

don't need the [] in the patindex statement
and could be
where patindex('% ' + @searchword + '%', ' ' + word) <> 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources