May 5, 2008 at 7:51 am
Dear Pals,
I have small requirement in my project.
I need to display the results of the WHERE clause based on percentage/ranking of exact match.
I mean the result set should be displayed based on percentage match.
For example i have the below table.
create table test
(
id int identity(1,1) primary key,
ename varchar(10)
)
insert into test(ename) select 'REG'
insert into test(ename) select 'xyz'
insert into test(ename) select 'abc'
insert into test(ename) select 'Reg'
insert into test(ename) select 'Regsxysn'
insert into test(ename) select 'psReg'
I need the output something similar as below
REG
Reg
Regsxysn
psReg
Any suggestions would be appreciated.
Thanks in Advance.
May 5, 2008 at 7:58 am
From the example given, I'd say the easiest way to do this would be:
select *
from dbo.Table
where Column like '%Reg%'
If you really need something smarter than that, then you need to define match characteristics.
For example, would "Rexg" be a match? 3 of 4 characters match your search string, which would be 75%.
How about "gre"? 100% match on characters, 0% match on sequence.
How about "We went to register our cars and handle some other chores. Joe got a vanity license plate." It does contain "reg", but it's a very, very small piece of the string. (The Like query above would return that one.)
How about "Rob eats eggs"? Has "r" "e" and "g", in the right sequence.
In summary, this isn't as easy as it sounds at first. You have to define what a "match" is.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2008 at 12:07 am
Thanks for the help!
May 6, 2008 at 12:13 am
you can use functions like SOUNDEX() and DIFFERENCE() if you want to know match %
May 6, 2008 at 5:25 am
Dear Squared,
You are perfect!
That is what i require.
Can u give me example for implementing this using a SELECT.
That would be a great help.
I tried out using Full Text containstable() but i need to list out all the possible values for giving the weightage search from the front -end, which i would not know at the time of search.
Thanks!
May 6, 2008 at 6:55 am
I'm not sure what you're asking for. I gave several examples of possible matches, and one select statement using Like.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply