[dba-VB] Splitting a name

Max,

The problems with names is that there are many variations and you cannot 
count on simple rules like that.

For example Mr. Juan Miguel de la Uariate.  Now you get another list 
with De la Uriarte Juan Miguel Sr.  You get the picture

I purchased a library to do the split.  The library performs the split 
nicely, handling such variations quite well.  What I need now is a 
strategy for reading rows or groups of rows, updating a single record at 
a time back to SQL Server.

In the absence of experience I am going to open a chunk of records,, 
perhaps 10K, with a startPK and EndPK to track the chunks into an ADO 
recordset.  I will read in 10K records (or some chunk size), then just 
iterate that recordset splitting each name.

My first attempt will be to dynamically build up an update query to 
write EACH RECORD back out, one at a time.  I am almost certain that 
this will not be fast enough but we shall see.

Once I have the code I will probably move the sql into a udf and call 
the udf passing the name parts in to the udf which will then write one 
record back to the table.  Also likely not fast enough but we shall see.

Last will be an attempt to build an ado recordset in memory, then have 
ADO write the entire recordset back to SQL Server.  If that is not fast 
enough I have run out of ideas.

Max Wanadoo wrote:
> John:
> No doubt there are many more cleverer answers then this and this may be
> tooooo slow for your needs.
> 
> Function jc()
>   ' will take the 1st series up to space as Xname, right series back to
> space as zname and bit in the middle as yname
>   Const conQuote As String = """"
>   Dim sql As String
>   sql = "update tblTest set "
>   sql = sql & "Xname = left(Searchname,instr(Searchname," & conQuote & " " &
> conQuote & ")-1) ,"
>   sql = sql & "Yname = Mid(searchname, InStr(searchname, " & conQuote & " "
> & conQuote & ") + 1, InStrRev(searchname, " & conQuote & " " & conQuote & ")
> - InStr(searchname, " & conQuote & " " & conQuote & ")),"
>   sql = sql & "Zname = mid(Searchname,instrrev(Searchname," & conQuote & " "
> & conQuote & ")+1)"
>   CurrentDb.Execute (sql)
> End Function
> 
> Max
>  
> 
> -----Original Message-----
> From: dba-vb-bounces at databaseadvisors.com
> [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Saturday, April 19, 2008 5:08 PM
> To: VBA
> Subject: [dba-VB] Splitting a name
> 
> I have a table with eight million records, with a single name field, and I
> have a library that splits the name into components.  What I need now is a
> generic method of getting the name out of a table and updating a specific
> set of fields back in the table.  It has to do so QUICKLY since I have to
> update 80 million names.
> 
> In this case I have a single table
> 
> Name
> 
> NamePrefix
> LastName
> FirstName
> MiddleName
> NameSuffix
> 
> Name already exists and contains a name in a consistent format LName FName
> MName.
> 
> The other fields I have added to my table.
> 
> So what I am looking for is a general strategy.  I assume I will open a read
> recordset with the PKID and the name field, then iterate through this
> recordset performing the split.
> 
> Once I do that should I:
> 
> Build a dynamic SQL Update query and execute that?
> 
> Build a stored procedure that I pass the PKID and the name parts to which
> then updates the data?
> 
> Use an ADO recordset to do this?
> 
> some other method entirely?
> 
> I am assuming at this point that because of the sheer size of the data I do
> not want to try and do this in an ado recordset.  Eight million records
> sitting in data at one time does not sound fun.
> 
> All ideas gratefully accepted.
> 
> --
> John W. Colby
> www.ColbyConsulting.com
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
> 

-- 
John W. Colby
www.ColbyConsulting.com