Splitting text field using SQL in Visual Basic (VB)

[Max]

Hi,
I have the following problem. In one table in Access db I have a field
(Name) which is a combination of "LastName FirstName". I am trying to
split this field and insert data into another table in two different
fields. I want to accomplish this with SQL in VB.
The problem is that sometimes FirstName(which will always be 1 char)
may not exist. So I might have something like this "Williams J" or
just "Williams".
So something like this won't work since I don't know if there is a
space after LastName or not:
"INSERT INTO table2 (LAST_NAME, FIRST_NAME) " & _
"SELECT " & _
"Left$([FULL_NAME], InStr([FULL_NAME], ' ')-1), " & _
"Right$([FULL_NAME], Len([FULL_NAME]) - InStr([FULL_NAME], ' ')) " & _
"FROM table1"

Can somebody please tell me if it is possible to do this test using
just SQL statement or do I have to fetch my table into a recordset and
spit it there.
Also VB complains about that -1 (or - InStr) that I have in my example
can you tell me what would a correct syntax be here.
Thanks for your help!

 

[steve]

here's the answer if you promise to never, ever, EVER cross-post again! <g>

*** this will catch most names correctly. it favors encountering more names
in the pattern of "Von Patten Herald"...which it will parse just fine. it
will *not* correctly catch names in the pattern of "Smithe J J" - the last
name in this case w/b "Smithe J" w/ a first name of "J". it's hard to write
a catch-all for names but the latter s/b encountered less often then the
former.

anyway,

hth,

steve

============================

sounds like you want to do this all in access using vba. under that
assumption, place the following in a module in your access db.

Public Function cleanName(ByVal fullName As String, _
Optional ByVal getLastName As
Boolean = True)
Dim i As Long
Dim firstName As String
Dim lastName As String
i = InStrRev(fullName, " ")
firstName = Right$(fullName, Len(fullName) - i)
lastName = Left$(fullName, i)
If i = 0 Then
lastName = firstName
firstName = vbNullString
End If
cleanName = Trim$(IIf(getLastName, lastName, firstName))
End Function

then, create a query in access (don't select any tables). view the query in
sql mode and paste the following:

INSERT INTO Table2(
LAST_NAME,
FIRST_NAME
)
SELECT cleanName(FULL_NAME) AS lastName,
cleanName(FULL_NAME, False) AS firstName
FROM Table1

you are allowed to call public procedures directly from w/n an access query
similar to a sql server udf.

 

[Max]

Thanks for your reply (sorry for cross posting I wasn't sure where
this post belongs to).
Actually I don't want to use VBA for it. I am writting code in VB.NET.
Reading text file (with new records) -> putting it into table ->
comparing it with another table that held previous records -> decide
which records to add/delete/update and put it into another table with
such description. Anyway everything work fine except that in the file
I have "LastName FirstName" as one field and that's how I need to
store it in the first table but then I need to split it...
All my code is written using SQL in VB.NET and I just do
ExecuteNonQuery() on my sql command. So I don't feel like fetching
stuff into dataset and analyze it and then update it. I feel like it
will take more time to execute the query. Of course, that's what I am
going to do if there is no way to write a query for it.
My main problem with the query is that if I use something like this
Left(Full_Name, InStr(Full_Name, ' ') - 1)
I get invalid procedure call message but if I remove - 1 it works ok
but I get space included at the end of Last_Name. Of course testing if
First_Name doesn't exist is another issue, I think Iff(InStr(...)>0)
might come in handy here. But for now I am trying to figure out that -
1 nonsense.

Thanks for the help.

 

[Rick Brandt]

Thanks for your reply (sorry for cross posting I wasn't sure where
this post belongs to).

Just a note here Max. Crossposting is generally considered perfectly ok and is
MUCH preferred to Multiposting which is to separately post the same question to
multiple groups. It is considered bad form to crosspost to more than 3 or 4
groups at a time so in my opinion you did just fine.

 

[John Spencer (MVP)]

Do it in two queries with criteria looking for a space in the Full_name.

OR use an IIF clause to identify which records have a space in the Full_Name field.

SELECT
IIF(Instr([Full_Name,' ')=0,[Full_Name], YourCurrentExpressionForLastName),
IIF(IIF(Instr([Full_Name,' ')=0,Null, YourCurrentExpressionForFirstName),
FROM ....

 

[Jeff Frent]

Hi,
I have the following problem. In one table in Access db I have a field
(Name) which is a combination of "LastName FirstName". I am trying to
split this field and insert data into another table in two different
fields. I want to accomplish this with SQL in VB.
The problem is that sometimes FirstName(which will always be 1 char)
may not exist. So I might have something like this "Williams J" or
just "Williams".
So something like this won't work since I don't know if there is a
space after LastName or not:
"INSERT INTO table2 (LAST_NAME, FIRST_NAME) " & _
"SELECT " & _
"Left$([FULL_NAME], InStr([FULL_NAME], ' ')-1), " & _
"Right$([FULL_NAME], Len([FULL_NAME]) - InStr([FULL_NAME], ' ')) " & _
"FROM table1"

Can somebody please tell me if it is possible to do this test using
just SQL statement or do I have to fetch my table into a recordset and
spit it there.
Also VB complains about that -1 (or - InStr) that I have in my example
can you tell me what would a correct syntax be here.
Thanks for your help!

This code works in Access, the only question I have is the NULL: IF SQL
will except Null as a valid value then this should work:

"Insert Into table2 (Last_Name, First_Name) " & _
"Select " & _
"IIF(Mid$([Full_Name], Len([Full_Name]) - 1,1) = ' ', Left$([Full_Name],
"Len([Full_Name]) - 2), " & _
"[Full_Name]), " & _
"IIF(Mid$([Full_Name], Len([Full_Name]) -1,1) = ' ', Right$([Full_Name],1),
NULL) " & _
"From Table1"

If the NULL does not work, you can try '' (that's two single quotes), in
either case you may have to be sure that the field [First_Name] will except
blank values...

hth,