Reverse Search!!!!! HELP - 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 2005 Forums

Transact-SQL (2005)

Reverse Search!!!!! HELP

Author

Topic

gagankhurana
Starting Member

8 Posts

Posted - 2007-04-04 : 11:06:31

Hi All
I have requirement to search on a text field in table. The point is that I want to get all the results even if i search for "REVERSE Text".
e.g. Whether i will search for "Bill Gates" or "Gates Bill"... I should get the same result set.
I tried with wild cards...but in vain...
Can any one give me an idea...how to accomplish it?
Gagan

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-04 : 11:10:31

[code]declare @t table
(
a varchar(40)
)

insert @t
select 'bill gates' union all
select 'bill clinton'

select * from @t where a like '%gates%' and a like '%bill%'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 11:12:01

REVERSE of "Bill Gates" is "setaG lliB".

Make use of a list parsing function, and the new CROSS APPLY operator.

Or

SELECT * FROM Table1
WHERE ColF = @Param1 AND ColL = @Param2 OR ColF = @Param2 AND ColL = @Param1

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-04-04 : 11:14:31

you can also use full text indexing for this

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

gagankhurana
Starting Member

8 Posts

Posted - 2007-04-04 : 11:26:55

Hi Peter
I want same result set for "Bill Gates" and "Gates Bill" , not for "setaG lliB" as you mentioned. Sorry if didn't clearify the question earlier.
Actually the scenario is as follows:
I have a table with varchar col, which contains data like this...
"Mr. Bill Gates"
"Mr. Bill Clinton"
"Bill Clintron"
"Bill Gates" and so on...
I have the input paramter for my procedure as "Gates Bill" or "Bill Gates". So what should i do that i will get same result set for both parameters.
And What is CROSS APPLY operator? Is it new in SQL Server 2005?
Gagan

quote:
Originally posted by Peso

REVERSE of "Bill Gates" is "setaG lliB".

Make use of a list parsing function, and the new CROSS APPLY operator.

Or

SELECT * FROM Table1
WHERE ColF = @Param1 AND ColL = @Param2 OR ColF = @Param2 AND ColL = @Param1

Peter Larsson
Helsingborg, Sweden

gagankhurana
Starting Member

8 Posts

Posted - 2007-04-04 : 11:28:02

Hi
Can you give me an example for it..

quote:
Originally posted by spirit1

you can also use full text indexing for this

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-04-04 : 11:35:24

look in books online = sql server help for exact syntax

however you have to build the catalog and index and then use contains or freetext to search for keywords.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 11:35:40

[code]declare @names table (nm varchar(200))

insert @names
select 'Mr. Bill Gates' union all
select 'Mr. Bill Clinton' union all
select 'Bill Clintron' union all
select 'Bill Gates'

declare @search varchar(200)

set @search = 'bill gates'

select nm from @names
cross apply dbo.fnParseList(' ', nm) as l
inner join dbo.fnParseList(' ', @search) as s on s.data = l.data
group by nm
having count(*) = (select count(*) From dbo.fnParseList(' ', @search))
order by nm[/code]

Peter Larsson
Helsingborg, Sweden

gagankhurana
Starting Member

8 Posts

Posted - 2007-04-04 : 17:32:50

You are great Peter!!!
Thanks a lot.

quote:

Originally posted by Peso

declare	@names table (nm varchar(200))

insert	@names
select	'Mr. Bill Gates' union all
select	'Mr. Bill Clinton' union all
select	'Bill Clintron' union all
select	'Bill Gates'

declare @search varchar(200)

set @search = 'bill gates'

select nm from @names
cross apply dbo.fnParseList(' ', nm) as l
inner join dbo.fnParseList(' ', @search) as s on s.data = l.data
group by nm
having count(*) = (select count(*) From dbo.fnParseList(' ', @search))
order by nm

Peter Larsson
Helsingborg, Sweden

gagankhurana
Starting Member

8 Posts

Posted - 2007-04-04 : 17:33:58

Thanks to all of you guys...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 19:00:52

Remember that this is a very inefficient way to do this.

Peter Larsson
Helsingborg, Sweden

gagankhurana
Starting Member

8 Posts

Posted - 2007-04-06 : 10:05:41

Then what is the efficient way Peter?
Can I do something else to achieve the same result??????

quote:
Originally posted by Peso

Remember that this is a very inefficient way to do this.

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-04-06 : 11:03:28

I feel i must repeat myself:
use full text search

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources