Similar Words - 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 2000 Forums

Transact-SQL (2000)

Similar Words

Author

Topic

Rekha
Starting Member

14 Posts

Posted - 2007-06-11 : 07:04:55

Hello,

I have a table lu_country_alias which has both correct & incorrect values for country from the look-up table lu_country.

If a wrong data from text file comes in
for eg:Inida,Ausrtia,Hong Kon etc
These are not present in the look-up table lu_country(which contains the actual value).
so they are placed in lu_country_alias with country_id=-1

select * from lu_country_alias
where country_id=-1

Now I have a requirement to merge the existing data & update correct countries.

Any Ideas??

Please help

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-11 : 07:42:09

which table do you want to update.....

pls..post the structure of two table , sample data and required output

--------------------------------------------------
S.Ahamed

Rekha
Starting Member

14 Posts

Posted - 2007-06-11 : 08:31:49

Please find the structure of 2 tables

CREATE TABLE [LU_COUNTRY] (
[Country_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Country_Code_2] [nchar] (2) NOT NULL ,
[Country_Code_3] [nchar] (3) NOT NULL ,
[Country_Desc] [nvarchar] (50) NOT NULL ,
[Do_Not_Contact] [nchar] (1) NULL ,
[ESE_Flag] [nchar] (1) NULL ,
[IQV_Vendor_Cd] [nchar] (2) NULL ,
[Date_Entered] [datetime] NOT NULL ,
[Entered_By] [varchar] (50) NOT NULL ,
[Date_Updated] [datetime] NOT NULL ,
[Updated_By] [varchar] (50) NOT NULL ,
[ISO_Code] [int] NULL ,
[Geo_Region_Id] [int] NULL ,
[NANPA_Flag] [bit] NULL CONSTRAINT [NANPA_Flag_dflt] DEFAULT (0),
CONSTRAINT [PK_LU_COUNTRY] PRIMARY KEY CLUSTERED
(
[Country_ID]
) ON [PRIMARY] ,
) ON [PRIMARY]
GO

CREATE TABLE [LU_COUNTRY_ALIAS] (
[Country_Alias_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Country_ID] [int] NOT NULL DEFAULT ((-1)),
[Country_Alias] [nvarchar] (100) NOT NULL ,
[Date_Entered] [datetime] NOT NULL ,
[Entered_By] [varchar] (50) NOT NULL ,
[Date_Updated] [datetime] NOT NULL ,
[Updated_By] [varchar] (50) NOT NULL ,
CONSTRAINT [PK_LU_COUNTRY_ALIAS] PRIMARY KEY CLUSTERED
(
[Country_Alias_ID]
) ON [PRIMARY] ,
CONSTRAINT [UQ_LU_COUNTRY_ALIAS_COUNTRY_ALIAS] UNIQUE NONCLUSTERED
(
[Country_Alias]
) ON [PRIMARY] ,
) ON [PRIMARY]
GO

The sample date in LU_Country table is
Country_ID,Country_Code_2,Country_Code_3,Country_Desc
==================================================================
-1 -- --- Unknown Country
1 AF AFG Afghanistan
2 AL ALB Albania
3 DZ DZA Algeria
4 AS ASM American Samoa
5 AD AND Andorra

The sample date in LU_Country_Alias table is
CountryAlias_ID,Country_ID,Country_Alias
==================================================================
833 -1 Austira
834 -1 CHAI
835 -1 Eduador
836 -1 Hong Kon
2 5 AD
3 3 AL

There will be a new column in this table which will tell the correct value it should map to.
As you can see the country_alias column is having wrong country names & has country_id=-1,its this country value which needs to be updated based on lu_country table

Thanx

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-11 : 08:50:14

Something like this may help:

Update a
Set Country_ID = c.Country_ID
from LU_COUNTRY_ALIAS a , LU_COUNTRY c
Where difference(c.Country_Desc, a.Country_Alias) = 4

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-06-11 : 09:00:27

"Any Ideas??"

Well my first approach would be to shoot the people sending you the rubbish data!

I think its one thing to autoMagically convert, say, UK to GB - its just a Nuance difference.

But how do you know that "Ausrtia" is "Austria" and not "Australia" - or even, phonetically speaking, Eurasia or somesuch?

Kristen

Rekha
Starting Member

14 Posts

Posted - 2007-06-11 : 10:02:27

Thanx for the immediate reply.

I will present the possible values for updation,the correct will be decided later.

Any other way out(other than shooting my client

)

Kristen
Test

22859 Posts

Posted - 2007-06-11 : 10:33:13

"other than shooting my client"

Where's the fun in that?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-11 : 11:06:45

Try using this fuzzy-search algorithm, which I developed specifically for matching typos and spelling variations. For this purpose, it is superior to the SOUNDEX algorithm:
http://sqlblindman.googlepages.com/fuzzysearchalgorithm

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources