common mistake

[phil page]

I know this is dim, but it happens.......
I have a name field like John Smith and really I would like to split it; like Smith, John so it sorts alpha. Its not quite so simple as the field maybe has Sid & Toni Smith or John M. Smith. I have fixed this problem before on other systems.
Whats the best approach.

Phil
_________________
phil

[RonIA]

(OOPS, sorry, I see this is in the BASE forum. Let me run and look...)

It would help if you let us know if you were in Calc, or Base, or a table in Writer.

I am assuming Calc, because this would be easiest...

If you have the names like John Smith in column A put the following in column B to extract the last name.
=TRIM(RIGHT(A1;LEN(A1)-FIND(" ";A1)))

Trim cuts off any leading or trailing spaces, Right takes characters from the right side of a text string, Len finds the length of the string, and Find tells you where the space is. So you are grabbing the last name.

To extract the first name...
=TRIM(LEFT(A1;FIND(" ";A1)))

I think this should get you on the right path and I'll leave it to you to figure out the special cases, like the ampersands...

Hope this helps.
_________________
Ron from Iowa, USA

[RonIA]

Now, I don't do this for just anyone...

I tried out some stuff and had to go to the HSQLDB documentation to see exactly what could be done in the HQSLDB engine (the built in db engine for OO.o)

Take a look at this:

SELECT RIGHT( "Users"."FullName", CHAR_LENGTH ( "Users"."FullName" ) - LOCATE( ' ', "Users"."FullName", 1 ) ) AS "LastName", LEFT( "Users"."FullName", LOCATE( ' ', "Users"."FullName", 1 ) ) AS "FirstName", "FullName" FROM "Users" "Users"

This query will pick out last name and first name from the field FullName in table Users.

Hope this is more helpful, it was certainly helpful for me.
_________________
Ron from Iowa, USA

[phil page]

Er,
Thanks it is in base, although I could presumably, export it and reimport it.
But I imagined a similar sort of macro, hinges on the ability to identify the delimiter which is in this case basically a space find " ", working from the right to left. I'm not familar with macros. I was thinking that this must be a very common mistake... so there would be a lot of expertise at knocking it into shape. It comes, of course from lack of forethought..... in this case not mine, but I think I have once done this, but once is enough.
Phil
_________________
phil

[phil page]

Looks pretty useful....
I'll try it tomorrow
Thanks Ron

Phil
_________________
phil

[RonIA]

You are welcome. In my previous life... I was frequently getting data with names combined and people asking to break them apart. Not a problem with 4-5 records, but 500,000 records, you want the database engine to manage it for you.

Let me know how it works for you. You will probably have to do some "magic" or at least some serious thinking to be able to pull out the ones with the ampersands and figure the logic to handle them.
_________________
Ron from Iowa, USA

[phil page]

Erm . I'm struggling a bit, this is an sql query?
and it gives me an error message, data could not be loaded 'cos of undefined function in ODBC. So is the problem 'cos its linked ODBC...........I guess it doesn't need to be, it was the way I set it up till I was confident I could handle things in base. I probably need to spend some time of a bit of reading around the subject

Phil
_________________
phil

[pdmirowsky]

I don't quite understand the language yet, but this problem is usually approached by counting backwards from the name field to the first space. Then procede to count back some more.

The the reason for this is that you may a two part last name

If there is only one letter and another space it is propabley the middle initial. Otherwise, you have encountered a split last name. Like "Mac Donald" or "van Nuyes".
_________________
Paul Mirowsky

[RonIA]

Well, I think you may have to look into the standard SQL to filter through ODBC. This is getting into an area I am not too familiar with. I just whipped up a standard Base file and looked into the HSQLDB syntax. Sorry.
_________________
Ron from Iowa, USA