Matching Names

Hi All,
I have a table with two columns that I want to match but am unsure of
how to.
The Columns are called "User_Name" and Managed_By" the user_name is
entered as "Fred Flintstone" while the Managed_By is entered are
"Flintstone, Fred".
To the human eye you can see that they are the same person but how can
i do that match in SQL?
I am using a SQL 2000 server
Here are 5 rows of data that I am trying to match from my table, there
are other columns in the table such as Row_Date, Acc_No
What I want to do is bring back all of the rows where the managed_by is
equal to the user_name
Thanks
Mark
Sample Data>>>>>>>>>>>>>>>>>>>>>>>
Managed_By User_Name
Ward, Kimberley Kimberley Ward
Pinder, Louise Rachel Brooks
Services, Credit Rob Mackey
Hatfield, Rebecca Joanne Fixter
Hatfield, Rebecca Rebecca HatfieldTry,
use northwind
go
declare @.t table (
Managed_By varchar(50),
[User_Name] varchar(50)
)
insert into @.t values('Ward, Kimberley', 'Kimberley Ward')
insert into @.t values('Pinder, Louise', 'Rachel Brooks')
insert into @.t values('Services, Credit', 'Rob Mackey')
insert into @.t values('Hatfield, Rebecca', 'Joanne Fixter')
insert into @.t values('Hatfield, Rebecca', 'Rebecca Hatfield')
select
*
from
@.t as a
where
[User_Name] = parsename(replace(Managed_By, ', ', '.'), 1) + ' ' +
parsename(replace(Managed_By, ', ', '.'), 2)
go
AMB
"Sh0t2bts" wrote:

> Hi All,
> I have a table with two columns that I want to match but am unsure of
> how to.
> The Columns are called "User_Name" and Managed_By" the user_name is
> entered as "Fred Flintstone" while the Managed_By is entered are
> "Flintstone, Fred".
> To the human eye you can see that they are the same person but how can
> i do that match in SQL?
> I am using a SQL 2000 server
> Here are 5 rows of data that I am trying to match from my table, there
> are other columns in the table such as Row_Date, Acc_No
> What I want to do is bring back all of the rows where the managed_by is
> equal to the user_name
> Thanks
> Mark
> Sample Data>>>>>>>>>>>>>>>>>>>>>>>
> Managed_By User_Name
> Ward, Kimberley Kimberley Ward
> Pinder, Louise Rachel Brooks
> Services, Credit Rob Mackey
> Hatfield, Rebecca Joanne Fixter
> Hatfield, Rebecca Rebecca Hatfield
>

Matching algorithm

I have a db full of names and identifiers. Was wondering if anyone could direct me towards a good dupe eliminating algorithm or bit of logic to look at?
Thanks!What will be your basis for eliminating duplicates ?|||Originally posted by rnealejr
What will be your basis for eliminating duplicates ?

Name dupes and identifiers, some names may be similar, some addresses may be simialr. Each row will have approximately 10 identifiers, but some will be nnull. It is fairly dirty data.|||But how do you determine which rows to delete (like keying off the most recent timestamp) or will it be a visual comparison (so will you need to route these duplicates to a holding table) ?|||Originally posted by rnealejr
But how do you determine which rows to delete (like keying off the most recent timestamp) or will it be a visual comparison (so will you need to route these duplicates to a holding table) ?

Yes I wil need to move them to a holding table. It will be compared visually using all.|||You should post more info about your table of duplicities.
Generic query looks like this

select d1.*
into ClearTable
from DuplTable d1
join
(
select IdCol1,IdCol2,...
from DuplTable d2
group by IdCol1,IdCol2,...
having count(*)=1
) x on (d1.IdCol1=x.IdCol1 or (d1.IdCol1 is null and x.IdCol1 is null))
and (d1.IdCol2=x.IdCol2 or (d1.IdCol2 is null and x.IdCol2 is null))
.
.
.
select d1.*
into HoldingTable
from DuplTable d1
join
(
select IdCol1,IdCol2,...
from DuplTable d2
group by IdCol1,IdCol2,...
having count(*)>1
) x on (d1.IdCol1=x.IdCol1 or (d1.IdCol1 is null and x.IdCol1 is null))
and (d1.IdCol2=x.IdCol2 or (d1.IdCol2 is null and x.IdCol2 is null))
.
.
.
select * from ClearTable
select * from HoldingTable|||Originally posted by ispaleny
You should post more info about your table of duplicities.


Awesome, I am going to try that format. My original table is pretty dirty. It is Lname, fname, SSN, address, city, state, country, phone number, etc., about 5 more identifiers. It is around 3 million records, some complete, some not. Spellings are different in some cases, middle names are there in other cases.

Thanks for your help.
:)

Matching a Views columns to its underlying tables columns

Hello,

Using SQL Server 2000, I'm trying to put together a query that will
tell me the following information about a view:
The View Name
The names of the View's columns
The names of the source tables used in the view
The names of the columns that are used from the source tables

Borrowing code from the VIEW_COLUMN_USAGE view, I've got the code
below, which gives me the View Name, Source Table Name, and Source
Column Name. And I can easily enough get the View columns from the
syscolumns table. The problem is that I haven't figured out how to
link a source column name to a view column name. Any help would be
appreciated.

Gary

select
v_obj.name as ViewName,
t_obj.name as SourceTable,
t_col.name as SourceColumn
from
sysobjects t_obj,
sysobjects v_obj,
sysdepends dep,
syscolumns t_col
where
v_obj.xtype = 'V'
and dep.id = v_obj.id
and dep.depid = t_obj.id
and t_obj.id = t_col.id
and dep.depnumber = t_col.colid
order by
v_obj.name,
t_obj.name,
t_col.namegaryderousse@.yahoo.com (Gary DeRousse) wrote in message news:<9ce1cc62.0311051041.2dd0f428@.posting.google.com>...
> Hello,
> Using SQL Server 2000, I'm trying to put together a query that will
> tell me the following information about a view:
> The View Name
> The names of the View's columns
> The names of the source tables used in the view
> The names of the columns that are used from the source tables
> Borrowing code from the VIEW_COLUMN_USAGE view, I've got the code
> below, which gives me the View Name, Source Table Name, and Source
> Column Name. And I can easily enough get the View columns from the
> syscolumns table. The problem is that I haven't figured out how to
> link a source column name to a view column name. Any help would be
> appreciated.
> Gary
>
> select
> v_obj.name as ViewName,
> t_obj.name as SourceTable,
> t_col.name as SourceColumn
> from
> sysobjects t_obj,
> sysobjects v_obj,
> sysdepends dep,
> syscolumns t_col
> where
> v_obj.xtype = 'V'
> and dep.id = v_obj.id
> and dep.depid = t_obj.id
> and t_obj.id = t_col.id
> and dep.depnumber = t_col.colid
> order by
> v_obj.name,
> t_obj.name,
> t_col.name

I don't believe that this information is available - sysdepends
records that the dependency exists, but not exactly what the
dependency is. The mapping of view to table columns could be 1:N or
M:N (or 1:0, in fact), so I would guess that MS decided that it wasn't
worth the effort to try and capture the detailed column mapping.

Simon|||Simon,

Thanks for the information, even though it wasn't what I wanted to hear.

Gary

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0311060041.35542cec@.posting.google.com>...
> garyderousse@.yahoo.com (Gary DeRousse) wrote in message news:<9ce1cc62.0311051041.2dd0f428@.posting.google.com>...
> > Hello,
> > Using SQL Server 2000, I'm trying to put together a query that will
> > tell me the following information about a view:
> > The View Name
> > The names of the View's columns
> > The names of the source tables used in the view
> > The names of the columns that are used from the source tables
> > Borrowing code from the VIEW_COLUMN_USAGE view, I've got the code
> > below, which gives me the View Name, Source Table Name, and Source
> > Column Name. And I can easily enough get the View columns from the
> > syscolumns table. The problem is that I haven't figured out how to
> > link a source column name to a view column name. Any help would be
> > appreciated.
> > Gary
> > select
> > v_obj.name as ViewName,
> > t_obj.name as SourceTable,
> > t_col.name as SourceColumn
> > from
> > sysobjects t_obj,
> > sysobjects v_obj,
> > sysdepends dep,
> > syscolumns t_col
> > where
> > v_obj.xtype = 'V'
> > and dep.id = v_obj.id
> > and dep.depid = t_obj.id
> > and t_obj.id = t_col.id
> > and dep.depnumber = t_col.colid
> > order by
> > v_obj.name,
> > t_obj.name,
> > t_col.name
> I don't believe that this information is available - sysdepends
> records that the dependency exists, but not exactly what the
> dependency is. The mapping of view to table columns could be 1:N or
> M:N (or 1:0, in fact), so I would guess that MS decided that it wasn't
> worth the effort to try and capture the detailed column mapping.
> Simon