June 10, 2008 at 11:43 pm
Hi All,
I have a table with company names and id.I want to compare a company name entered by a user with that in a table and find the best possible match of the user's company....shud I do this on front end or I shud use a procedure at SQL.
Regards,
Ahmad
Regards,
[font="Verdana"]Sqlfrenzy[/font]
June 10, 2008 at 11:55 pm
I certainly recommend a stored procedure.
June 11, 2008 at 12:04 am
may i have the reason......bcoz i wud hv to us a cursor...if i do it at sql
Regards,
[font="Verdana"]Sqlfrenzy[/font]
June 11, 2008 at 12:12 am
Ahmad Osama (6/11/2008)
may i have the reason......bcoz i wud hv to us a cursor...if i do it at sql
Why would you need a cursor?
_____________
Code for TallyGenerator
June 11, 2008 at 12:27 am
ok...so u mean that i can navigate thru the table using a while loop on the identity column...or there is any other way...
Regards,
[font="Verdana"]Sqlfrenzy[/font]
June 11, 2008 at 1:07 am
please give us some detalis of the comparison you have to do... however i think sp is better
June 11, 2008 at 10:33 am
It seems your COMPANY table schema as:
CompanyID CompanyName
1 abc
2 bcd
....
You can add an index on [CompanyName], and use a stored procedure to search.
June 11, 2008 at 10:43 am
Use a stored procedure,
with a LIKE clause in the where statement
or if you want better matching you can use character matching or pattern matching.
Post you table DDL and some sample data for further help.
June 11, 2008 at 2:50 pm
These guys have you on the right track here. You definately don't need a cursor or loop to do this. If you need additional help with this, please post your table DDL, index scheme, and some sample data. Here's a link to best practices for posting sample data/code:
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
June 11, 2008 at 8:59 pm
I would suggest using the SOUNDEX or DIFFERENCE functions. And you do not need a cursor or a While Loop, just use set-based processing.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 11, 2008 at 11:29 pm
rbarryyoung (6/11/2008)
I would suggest using the SOUNDEX or DIFFERENCE functions. And you do not need a cursor or a While Loop, just use set-based processing.
Thnks for all the valuable suggestion....
I am using Difference but it is unable to differentiate between 'Sai word'
and 'sai impex' ....below is the procedure iam using...
alter proc usp_strsrch
@userstr varchar(100),
@strcmp varchar(200),
@iCityid int,
@returnstr varchar(8000) OUTPUT
AS
Set nocount on
Declare @count int,@i int,@flag int,@tmpstr varchar(100),@tmpcustid varchar(100)
create table #temp(Sno int identity(1,1),iCustomerid int ,sCustomercompname varchar(150))
insert into #temp
select iCustomerid,sCustomercompname from CustomerMaster C inner join Citymaster Ci
On C.iCityid=Ci.iCityid where Ci.iCityid=@iCityid and sCustomercompname like '%' + @strcmp + '%'
select @count=count(*) from #temp
set @returnstr=''
set @i=0
While @i<@count
BEGIN
select @tmpstr=sCustomerCompName,@tmpcustid=iCustomerid from #temp where sno=@i + 1
set @flag=Difference(@tmpstr,@userstr)
if (@flag > 3)
Begin
set @returnstr=@returnstr + @tmpcustid + ','
End
Set @i = @i + 1
End
drop table #temp
Regards,
[font="Verdana"]Sqlfrenzy[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply