OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

common mistake

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
phil page
General User
General User


Joined: 16 Nov 2005
Posts: 8
Location: North Yorkshire

PostPosted: Sat Jan 14, 2006 12:51 am    Post subject: common mistake Reply with quote

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
Back to top
View user's profile Send private message
RonIA
Super User
Super User


Joined: 28 Sep 2004
Posts: 925
Location: Iowa, USA

PostPosted: Sat Jan 14, 2006 4:40 am    Post subject: Reply with quote

(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
Back to top
View user's profile Send private message
RonIA
Super User
Super User


Joined: 28 Sep 2004
Posts: 925
Location: Iowa, USA

PostPosted: Sat Jan 14, 2006 6:04 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
phil page
General User
General User


Joined: 16 Nov 2005
Posts: 8
Location: North Yorkshire

PostPosted: Sun Jan 15, 2006 2:32 am    Post subject: common mistake Reply with quote

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
Back to top
View user's profile Send private message
phil page
General User
General User


Joined: 16 Nov 2005
Posts: 8
Location: North Yorkshire

PostPosted: Sun Jan 15, 2006 11:23 am    Post subject: commom mistake Reply with quote

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

Phil
_________________
phil
Back to top
View user's profile Send private message
RonIA
Super User
Super User


Joined: 28 Sep 2004
Posts: 925
Location: Iowa, USA

PostPosted: Sun Jan 15, 2006 2:38 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
phil page
General User
General User


Joined: 16 Nov 2005
Posts: 8
Location: North Yorkshire

PostPosted: Mon Jan 16, 2006 10:26 am    Post subject: common mistake Reply with quote

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
Back to top
View user's profile Send private message
pdmirowsky
Newbie
Newbie


Joined: 16 Jan 2006
Posts: 1
Location: New Jersey

PostPosted: Mon Jan 16, 2006 12:49 pm    Post subject: Seperating Names Reply with quote

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
Back to top
View user's profile Send private message
RonIA
Super User
Super User


Joined: 28 Sep 2004
Posts: 925
Location: Iowa, USA

PostPosted: Mon Jan 16, 2006 1:49 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group