Comparing Strings (Advanced Soundex)

Hello,

I need to compare movie names from two systems. In both systems these names are entered manually by operators. I would like to compare them and give a rating on how close these names are equal.

Stripping special characters, and spaces is just not enough. It can happen that they key in sligthly different names. I've tried to use soundex but as we have over 15000 movie titles over the years i'm getting to many equal soundexes to use this as a comparison key.

Any ideas if there are techniques to do this ...

Kind Regards

See if these will help you out any:

http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart2.asp

http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart3.asp

Comparing Money

Hi,

I have a field in database money. When I enter value for it the amount entered is for example 20.000. How can I compare this value with noraml vaules i.e. like 20 in my search engine. Will I need to convert it to varchar and then compare it or is there some other way. Also if I need to convert it to varchar, how can I do it?

Thanks in advance,
UdayIt seems to me that you don't need to do any conversion. This code works as expected for me:

DECLARE @.myMoney Money
SELECT @.myMoney = 20.000
-- this returns Found
SELECT CASE WHEN @.myMoney = 20 THEN 'Found' ELSE 'Not Found' END
-- this returns Not Found
SELECT @.myMoney = 20.500
SELECT CASE WHEN @.myMoney = 20 THEN 'Found' ELSE 'Not Found' END
-- this returns Not Found
SELECT @.myMoney = 2.000
SELECT CASE WHEN @.myMoney = 20 THEN 'Found' ELSE 'Not Found' END

Or are you saying that in my second example you need to return Found?

Terri|||Hi,

What I want to do is if someone enter 20 and searches, all the value in money datatype with 20.000 should come out as a result.

Thanks in advance,
Uday.|||Maybe you should show us the code for this that you have that is not working the way you need. This way we can focus on your exact issue.

Terri|||Hi,

The code is as below:
select * from test where price='26' in which price is of datatype money and the value in the database is 26.0000. Hope you will be able to understand my problem.

Thanks in advance,
Uday|||If price is datatype money then you should not enclose 26 in single quotes. With the single quotes the database is forced to try to do an implicit conversion from varchar to money for the comparison, which it will not do. Remove the single quotes and you should have better luck.

select * from test where price=26

Terri|||Hi,

I tried that, it's working.

Thanks
Uday.

Comparing data in table with validation table

Hi all

I have a customer table with a postcode and a suburb fields and cutomer info which is manually entered by data entry people...

I am trying to compare the entries against a postcode table with the correct postcodes which have fields postcode and suburb and based on the postcode entered in the customer table it should be the same as the suburb in the postcode table, if they are not the same output them to a table for manual checking..How would I go about this

thanks

You could do something like:

select *

from yourtable

where not exists (select *

from postCode

where postCode.postCode = yourTable.postCode)

etc.

As an aside, I would probably consider having a transition phase where you have a table for data entry, then a table that houses the final data, so you never have bad values in your actual OLTP database, just in the transition data, which you would delete in the process of putting it in the main table.

|||Would the above work if the postcode table contains all possible postcodes but the customer table may contain 1000 off the possible 16000 postcodes.|||

Well, yeah, but you need to be able to form the set of 1000 possible rows that are legal and use it in the EXISTS rather than all rows. So something like:

select *

from yourtable

where not exists (select *

from postCode

where postCode.postCode = yourTable.postCode

and meets1000criteria = 1)

Or you could build a view for the valid postalCodes.