Algorithams using T-SQL

Question

Algorithams using T-SQL

Mkumari

Hall of Fame

Points: 3498
More actions
September 9, 2004 at 11:04 am

#66204

Hi,
I am wondering if I can write an algoritham which checks for the name match and gives out the result to what extent the search criteria matches the results.
For Example: My search criteria is 'Mike' and I got the following results
1. Mike Mathew
2. Mikeal Mathew
3. Mike Mathew Mathew
The % match is 1. 50%,
2. less than 50%,
3. Less than 50% .
Can we do this using SQL.

Thanks,
MK

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Aaron Templeton SSCarpal Tunnel Points: 4655 More actions · Answer 1

Yes, but its probably ugly. Create a function to do whatever string analysis you crave for determining your percentage and then call that function passing the string column as a parameter returning the result as your match percentage resultant column. Its clean except that the function will likely give you fits to get just right. You will probably have to walk the string within a WHILE loop and may "need" nested functions (I think nested functions are allowed).

Mkumari Hall of Fame Points: 3498 More actions · Answer 2

Thank you for the input Aaron. Can you please give me the sample code for creating the function.

Thanks,

MK

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 3

If you want to make life really diffiicult and you had a lot of lengthy data to check, you could use Full Text Searching, which can return a RANK for you. But it would return a different % to what you dictated.

But briefly, you might be able to get that percentage with:

LEN(SearchParameter) / LEN(NameColumn) * 100

That would tell you what percentage of the NameColumn was SearchParameter. Sort of. By length anyways.

Julian Kuiters
juliankuiters.id.au

Aaron Templeton SSCarpal Tunnel Points: 4655 More actions · Answer 4

I've provided a function that uses a Split function we use. It works with the example provided but may not ultimately be what you need. You will have to determine what logic you ultimately require and adjust the MatchPercent function accordingly.

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

--This will break a list into its components returning the results in a single columned table.

--For example if @Delim is given as a space it will return a table of all words in the given "sentence".

CREATE FUNCTION Split

(@List varchar(1000)

, @Delim char(1)

)

RETURNS @Results table

(Item varchar(1000))

AS

begin

declare @IndexStart int

declare @IndexEnd int

declare @Length int

declare @Word varchar(1000)

declare @Kill int

set @IndexStart = 1

set @IndexEnd = 0

set @Length = len(@List)

set @Kill = 0

while @IndexStart <= @Length

begin

set @Kill = @Kill + 1

if @Kill >= 999 return -- hard limiter just in case

set @IndexEnd = charindex(@Delim, @List, @IndexStart)

if @IndexEnd = 0

set @IndexEnd = @Length + 1

set @Word = substring(@List, @IndexStart, @IndexEnd - @IndexStart)

set @IndexStart = @IndexEnd + 1

INSERT INTO @Results

SELECT @Word

end

return

end

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

CREATE FUNCTION MatchPercent

(@FullDesc varchar(1000)

, @MatchPart varchar(50)

)

RETURNS smallint

AS

begin

declare @WordCount int

declare @PartialMatches int

declare @FullMatches int

declare @Percent smallint

declare @Words table ( AWord varchar(1000) )

INSERT INTO @Words

SELECT * FROM dbo.Split( @FullDesc, ' ' )

SELECT @WordCount = COUNT(*) FROM @Words

set @PartialMatches = 0

set @FullMatches = 0

SELECT @PartialMatches = @PartialMatches + case when AWord LIKE '%' + @MatchPart + '%' AND AWord != @MatchPart then 1 else 0 end

, @FullMatches = @FullMatches + case when AWord = @MatchPart then 1 else 0 end

FROM @Words

set @Percent = ( ( @FullMatches + ( @PartialMatches * 0.5 ) ) / @WordCount ) * 100

return @Percent

end

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Then your query would be something like the following.

SELECT dbo.MatchPercent( TheDesc, @Portion ) AS Percent, TheDesc

FROM MyTable

WHERE TheDesc LIKE '%' + @Portion + '%'

Mkumari Hall of Fame Points: 3498 More actions · Answer 5

Mkumari

Hall of Fame

Points: 3498

September 10, 2004 at 2:30 pm

#522639

Thank you Aaron for your help.

MK

Mkumari Hall of Fame Points: 3498 More actions · Answer 6

Hey Julian, thank you for your input, Can you give me a example of the full text search.

Thanks,

MK

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 7

Hi MK

I don't have any full-text search catalogs at the moment, so I can't help you further with this sorry.

Read up about in BOL and test it out on a developement server.

Julian Kuiters
juliankuiters.id.au