Spliting column on import - 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

Import/Export (DTS) and Replication (2000)

Spliting column on import

Author

Topic

cgunner
Yak Posting Veteran

95 Posts

Posted - 2005-07-28 : 09:56:13

I have some data that I am trying to import into a table but the raw data has the full name in one column. Has anyone ran across an issue where you had to do an import using DTS from one column to three?

Sample:

DUFFY, JOHN P
DORSEY, ANTHONY W
DOLL, DANIEL W
DICRESCENZO, RONALD A
DENNIS-ESCOFFIER, SHIRLEY
DEE, BRUCE D
CROSBY, DONALD L
CROOK, THOMAS M
HILL, CHRISTINE E
CREWS, MICHAEL E

Kristen
Test

22859 Posts

Posted - 2005-07-28 : 10:30:47

Pull the data into a staging table and post-process it with SQL into the "actual" table?

Splitting names is a minefield ... what happens to L' and La This and people with christian names like Jean-Paul - maybe your data is reliably "Surname then comma then a christian name then optionally space and one initial"??

I'll eat my hat if its 100% uniform though

(and if not Post Processing it will make it much easier to have several "gos" to catch all the variations)

Kristen

cgunner
Yak Posting Veteran

95 Posts

Posted - 2005-07-28 : 10:59:13

How does this look?

Function Main()

Dim FullName
Dim FirstName
Dim LastName
Dim MidInitial
Dim Suffix
Dim Pos
Dim Pos2
Dim Pos3

strFullName = DTSSource("Col001")

Pos = InStr(1, strFullName, ",", vbTextCompare)
If Pos = 0 Then
Pos = Len(strFullName) + 1
End If
LastName = Trim(Left(strFullName, Pos - 1))

Pos2 = InStr(1, LastName, " ", vbTextCompare)
If Pos2 Then
Pos3 = InStr(Pos2 + 1, LastName, " ", vbTextCompare)
If Pos3 Then
Suffix = Right(LastName, Len(LastName) - Pos3)
LastName = Left(LastName, Pos3 - 1)
Else
Suffix = Right(LastName, Len(LastName) - Pos2)
LastName = Left(LastName, Pos2 - 1)
End If
End If

Pos2 = InStr(Pos + 2, strFullName, " ", vbTextCompare)
If Pos2 = 0 Then
Pos2 = Len(strFullName)
End If

If Pos2 > Pos Then
FirstName = Mid(strFullName, Pos + 1, Pos2 - Pos)
MidInitial = Right(strFullName, Len(strFullName) - Pos2)
End If

'
' suffix handling
'
Select Case UCase(Suffix)
Case "JR", "SR", "II", "III", "IV", "MD", "PHD", "PH.D", "M.D."

Case Else
If Not IsNumeric(Left(Suffix, 1)) Then
LastName = LastName & " " & Suffix
Suffix = ""
End If
End Select

DTSDestination("LName") = (LastName)
DTSDestination("FName") = (FirstName)
DTSDestination("MName") = (MidInitial)
DTSDestination("Suffix") = (Suffix)

Main = DTSTransformStat_Ok
End Function

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources