Get names out of a field seperated by a character

[FastEddie]

I am working with a table that has names stored like this:

Dent~Aurthur
Servo~Tom
Gently~Dirk

I need to run a select query that will take the Name field and split it up so that it puts the Authur in a FirstName Field and Dent in the LastName field. Is that possible using a query?

[Ishamael]

This is on what database? Anyhow this would work with MS-SQL 2005.

Table_1 has a column called bah with data like 'Anderson~Mr'
Table_2 has 2 columns which it inserts the names into.
SUBSTRING(string,start,end) -- gets you part of a string between end and start
LEN(string) -- gets you the length of the string

INSERT INTO Table_2
SELECT SUBSTRING(bah, CHARINDEX('~', bah) + 1, LEN(bah)) AS Firsty,
SUBSTRING(bah, 1, CHARINDEX('~', bah) - 1) AS lasty
FROM Table_1

Of course I doubt this would work on anything bar MS-SQL, it does however give you the basic idea of what to do.

I am not sure if Access etc provide T-SQL, however at the worst you could use ADO to pull out records, split them using VB Mid() etc and insert..
There is probably a better way then that.
Me and Access don't get on sorry.