Jan Bassez wrote:
So, the only way is splitting the fields in the database...
hmm...
I've got almost 1500 records already....
First time I use a database program in fact.
additional question:
what should I do with fields who contain this: PV 522 (op.268)
I don't know what this code means, but if it means what I *think* it
means, then you're looking at *another* field for this too :)
As for how to split things up, there are SQL functions to find
characters inside a string. In MySQL, there is the locate() function. So
if you knew that all your *string* data was on the left, and your
*numeric* data was on the right ( and they're separated by a space ),
then you could locate the position of this space like this:
locate( ' ', FIELD_NAME )
where FIELD_NAME is the name of the field in your table. This will give
you a *position* of the 1st space ( note there's a space in between the
2 quotes ). That's your 1st step. Do something like:
select FIELD_NAME, locate( ' ', FIELD_NAME ) as FirstSpacePosition from
TABLE_NAME
to see what results you get.
From here, there are a number of things that you can do. If you're not
comfortable with SQL, then perhaps the best way is to create a new field
in the table, called 'FirstSpacePosition', and the populate it with
something like:
update TABLE_NAME set FirstSpacePosition = locate( ' ', FIELD_NAME )
This will allow you to open the table directly and see what's going on.
Next you want to add some more fields - we don't want to modify any data
yet, so create some fields like:
StringPart, NumericPart, SomeOtherPart ... etc as required.
Then use the left() function to extract your string part, from the start
of the field to the 1st space:
update TABLE_NAME set StringPart = left( FIELD_NAME, FirstSpacePosition
- 1 )
Note the '-1' ... that's because we don't want to include the space in
the string ... we want to go one position back from the space.
Now have a look at the table - the new StringPart field should be
populated. Now we have to get the next part. The locate() command ( at
least in MySQL ) lets you specify a starting position for the locate
operation, so you can find the *2nd* space in the string with something
like:
locate( ' ', FIELD_NAME, FirstSpacePosition + 1)
Also note the '+1'. We have to add one to the FirstSpacePosition,
otherwise our locate() will simply find the original space again.
Next you might need to use the substring() ( again, this is a MySQL
function - I don't use Base, so I don't know if it's the same function
in Base ) to extract the stuff you're after. With a combination of
locate(), left(), and substring(), you should be able to break your
field up into as many separate parts as you need ... as long as
everything is in a predictable order. Otherwise, there's always manual
editing of data ... it's annoying, but it's better to fix your table
design while things are small.
Dan
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
