Can I use SQL to subdivide a text field ? 1

[TheBridge]

Hello all

I would like to subdivide a text field from it's current format, which could be any of these

Xxxxxxx
X Xxxxxxxx
X X Xxxxxxxxx
X X X Xxxxxxxxx
X Xxxxxx-Xxxxx

into two text fields - initials and surname.

Can I use SQL to do this ?

 

[PaulBricker]

Could you provide us with some actual value instead of the X's so we can see what differences you are demonstrating.
Thanks

Paul

 

[TheBridge]

Here you go ...

ID Name

1 S F Abbas
2 C Byrne
3 V N J Petty
4 A B D'Arcy
5 B N El-Eliwi

I would like to turn these into

ID Initials Surname

1 S F Abbas
2 C Byrne
3 V N J Petty
4 A B D'Arcy
5 B N El-Eliwi

Hope this clarifies things

 

[PaulBricker]

This problem has come around many times in these forums. Because of the inconsistency of the data, it's almost impossible to parse the initials out with 100% accuracy. You would write something that checked for the last Upper Case value and then take that and put it into a new field but that would bomb on two of your names. That's about as close as you could get. Let me know what you want to do and I can write the routine but then you would have to clean it up manually.

Paul

 

[Golom]

An alternative strategy would be to find the last space in the string and take everything to the right of it as the last name.

 

[th1011]

I have the exact opposite problem and would like to merge two fields into one. Any help on that would be appreciated.

 

[PaulBricker]

Looking for the last space will improve your accuracy but still not 100% (there are certainly last names with spaces in them) but his routine will get you started. You would add it to a module and then call it from the query.

Function GetLast(strSource As String) As String
Dim I As Integer
Dim OneChr As String
OneChr = " "
For I = Len(strSource) To 1 Step -1
If Mid(strSource, I, 1) = OneChr Then
GetLast = Right(strSource, Len(strSource) - I)
Exit Function
End If
Next I
GetLast = strSource

End Function

Paul

 

[PaulBricker]

th1011, to merge fields together, in a new column in a query you would put
MyMergedFields:[Field1] & " " & [Field2] & " " & [Field3}

This would merge three fields with a space between them.

Paul