Inserting code within a database field 3

[Phil Thoms]

Hello, I'm using VFP 6 and have a character field in a database which contains text and ends with a number or letter/number like so:-
xxxxxxxxxxxxxxx 123
xxxxxxxxxxxxxxxxxx 236
xxxxxxxxxxxxxxxxxxxxxx R551

etc., etc.

I wish to put a code one space before the end code, eg:- xxxxxxxxxxxxxxxx (DD) 123. There are three different codes to be used and will be based on another code field which has already been input.
I would like to know if there is a quick method of doing this?

Many thanks

 

[Mike Lewis]

More information please.

How do you recognise the existing end-code? Is it always the same number of characters? Is there always exactly one space before the end code? Can the end-code itself contain a space? And is it possible to have spaces anywhere else in the string?

The solution is probably going to be similar to those suggested in your thread184-1739435, but we need to know the constraints in order to answer it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mmerlinn]

Here is one possible way assuming that there is only ONE space in the existing field immediately before the current ending number/text:

[tt]textfield = "xxxxxxxxx 123"
textcode = "(DD)" + " "

textfield = STUFF(textfield, RAT(" ", textfield), 0, textcode)[/tt]​

If there is none, or more than one space, the solution becomes more complicated.

mmerlinn

http://mmerlinn.com

Poor people do not hire employees. If you soak the rich, who are you going to work for?

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Raymond

 

[Phil Thoms]

Mike,
To answer your questions in order:-
The existing end-code has already been input and can be 1 to 4 chars.
Not always same number of characters, can be between 1 and 4 char long.
Yes there is always a space before the end-code.
End-code will not contain a space.
There are spaces in the text string before the end-code.
A typical string as of now is:-
Newport Corporation 116
I would like it to read:- Newport Corporation (newcode) 116

Hope it is more understandable.

Thanks.

 

[mmerlinn]

Philthoms:

Based on your answers to Mike, code similar to what I submitted 4 minutes after his reply should work for you. You may need to juggle it a little for it to work as I have not tested it for bugs.

mmerlinn

http://mmerlinn.com

Poor people do not hire employees. If you soak the rich, who are you going to work for?

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Raymond

 

[Phil Thoms]

Hello mmerlinn,
You sent your reply to a previous query so I couldn't find it for a while. However I will try the code you have written.
Thanks

 

[mmerlinn]

Philthoms:

The code posted in THIS thread should solve your problem in THIS thread. The code posted in the OTHER thread will not work here to solve this problem but will work for your problem in the OTHER thread.

Are you thoroughly confused yet?

mmerlinn

http://mmerlinn.com

Poor people do not hire employees. If you soak the rich, who are you going to work for?

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Raymond

 

[Olaf Doschke]

Cautious,

Like mmerlin used in his STUFF solution, the position of the rightmost space is determined by RAT(), that works fine for a value like "Newport Corporation 116", but if that value is in a char field, that has trailing spaces. So the value in a table char field rather is "Newport Corporation 116 " and RAT() then will determine the rightmost space, which is at the end of the field, not the space right before the 116.

Therefore you have to use an RTRIM of the field before using RAT to find the rightmost space of the value, after the trailing spaces are removed.

acode = "(DD)"
Update yourtable SET yourfield = STUFF(yourfield, RAT(" ", RTRIM(yourfield)), 0, acode)

Bye, Olaf.

 

[Phil Thoms]

Not too confused (hopefully). I'll try what you have suggested for THIS thread.

Thanks, much appreciated.

 

[Olaf Doschke]

Hmm, I posted lots more space to demonstrate a char field mostly will have many trailing spaces, as it has to be dimensioned for the longest values, but the forum removed all but one.

So I'll try different. If you store "Newport Corporation 116" into a char(200) field, you'll have "Newport Corporation 116 ...<<many spaces to pad up to 200 chars>>... " in the field and rat(" ",field) would always be 200. Only, if the value is using the char(200) field fully and the last char of the last code ends at the 200th char, RAT will be the last space before that.

People often forget a char field is not only what you see, it includes as many spaces as are needed to pad to the field width. This space is reserved in each record. VFP9 introduced varchar fields, which don't have the trailing spaces, but as you're in VFP6 you surely have a char field.

As a side note, VFP9 even pads varchar fields, but with chr(0), it doesn't return these pad chars as the value of the field. The only thing in which you may also not need the RTRIM is memos, as memos are variable length and end at the end of the value you insert, they don't pad to a certain length. It won't hurt to RTRIM then, anyway.

More off topic: That's one of the reasons the memo field values can't be stored in the dbf and are put into a separate FPT file. The DBF only has fixed length fields to have a fixed recsize and be able to position to record N fast with the file offset HEADER()+(Recno-1)*Recsize(). The memo field has 4 byte in the dbf, which merely are a pointer offset into the fpt file to have that advantage. It's one of the things making DBFs fast, you can directly know the position of record N, you don't have to traverse a concatenated list.

Bye, Olaf.

 

[Olaf Doschke]

And again, as I already warned in the previous thread, if your field is dimensioned rather short, inserting something can lead to loss of characters at the right side. The SQL engine will not warn or error about that, it simply stores as much as fits into a char field. Even though it could know a value is too long to fit, because it's aware of the field width and pads to it. If it has a too long value it'll just cut off.

Bye, Olaf.

 

[Phil Thoms]

Thanks Olaf,
The field in question is only 50 characters in length. I'll be testing the code soon. Database is in use at the moment.

Many thanks

 

[Dan Freeman]

I'll go out on a limb here and point out the purist side.

In this thread and your previous thread you appear to be combining different data elements into a single field. That's generally the WORST possible way to store data. It would be like putting city, state and zip code in one field:

"Los Angeles CA 90045"

How the devil will you ever sort on zip code, or count the number of entries in CA?

It's generally better to store each data element as separate fields and combine them when they're used on forms, reports, labels, whatever.

 

[Phil Thoms]

Hello danfreeman,
Yes I see where you are coming from. However this is not about postcodes or zip codes or sorting. It is about fleets of vehicles eg "Newport Corporation (GG) 116" which means vehicle no.116 in the Newport Municipal fleet which is in Reg code area GG and part of South Wales. These items of text never require sorting and have remained in this format for 70 years. They form parts of large listings usually sorted in registration order or month/year order which are contained in separate fields. I research mainly British buses and coaches going back to 1904 and create databases containing varied information for many regions in the UK. There are times we filter out undertakings such as Newport, London etc which is easy to achieve.
Many thanks for your assistance and interest, it is much appreciated.

 

[Olaf Doschke]

Dan is right, and it doesn't matter if the topic is addresses or vahicle classification. The single values have a meaning you have described and storing them separate you can sort and search data better and faster with independant indexes.

Eg searching "(GG)" $ vehicle is an option, if you have the GG code in the vehicle field, but an & search always is slow compared to a areacode = "GG" search optiimzable with an index on codearea.

And if you have all data in separate fields you can bring them together for reports, which then is a valid need to do that. Eg via textmerge you can put together vehicle=Textmerge("<<fleet>> (<<areacode>>) <<vehicleno>>")

There is a reason adress data is stored in separate fields and there are reasons for data normalisation in general and you're hitting mr boyd and mr codd in the face if you go the opposite direction, besides introducing problems you'll face yourself in the future.

Bye, Olaf.

 

[Olaf Doschke]

Even if you put together data from the single values as you do now, for whatever reason you could simply do it fro left to right, then you wouldn't need STUFF and complicated terms to find the position where to stuff in data. You simply concatenate from left to right, then all you need is ALLTRIM() and +. Actually you can for example combine a firstname and lastname with firstname-(" "+lastname) instead of alltrim(firstname)+" "+Alltrim(lastname), so you even can concatenate the padded field values without alltrimming them by the nature of the - operator for string concatenation. Also a rarley known feature of VFP.

So:
1. It's easier to search in distinct fields with separate and many indexes. SQL-Select can make use of many indexes in a WHERE clause opposed to FOR optimiation and opposed to non optimizable $ instring search
2. It's easier to put together distinct fields to a concatenated value.
3. It's easier to do 2. from left to right, simply using +,- and alltrim, textmerge and transform, no need for AT/RAT position determination and STUFF, then.

We can only assume you're in a state with data somewhere in between being distinct values and the concatenated value you tend to go. You're going in the wrong direction, you'll optimize your data, if you spearate the values that are already combined.

So dan has really put the finger on the weak point of what you're currently accomplishing.

One more note, since you're at VFP6, you don't have textmerge() as function, but you have TEXT...ENDTEXT, and you have \ and \\ commands, see the help.

And one more side note: "field in question is only 50 characters". You can test if that's enough, if you know how much characters you're adding as worst case. Eg a space from the previous thread and a 4-5 letter code pkus another space, say 10 chars, RIGHT(field,10) has to be empty to add 10 chars somewhere without cutting off something, so simply BROWSE FOR NOT RIGHT(field,10)==SPACE(10) and you'll see all records that won't be able to hold the extended value. There is no need to wait and test, and how would you determine a loss afterwards? It won't be obvious if "...some company...(some area) 12" is really about vehicle 12 or it was vehicle 120-129 or even higher.

Another advantage of putting together the whole name when you need it is you'd not create it in a limited width field but as a string expression in memory, which is limited to about 16 million characters, a ridiculous limit for anything you print on a line of a report.

Bye, Olaf.

 

[Phil Thoms]

Hello Olaf,
I do know what you are saying and thoroughly agree with your comments. I have been suggesting separate fields over an over for several years to the people that run the organisation who provide this info. Their listings and publications contain data in certain formats and have been the same for many years before the advent of databases and computers. I do use expressions similar to "(GG)"$vehicle for filtering purposes mainly to check on certain data. In the old days everything was done on typewriters and then word processors, ditto marks were used, etc. I do know that it should not matter how I choose to store the data but if I disappear others may be confused with a database setup as many people before me have used Excel and still do. I have used FoxPro for many years because of its flexibility. I have thought about using Excel which is good for many projects but I think in the long run a database is far better. On occasions I do have to export to Excel for some of the members.
HOWEVER, I think I'm at last getting through to them, there is now a new chairman and his thinking is compatible with mine regarding this matter. So I think my patience may yet be rewarded.
I hope this info goes a long way to explaining the situation and I'm sure I'll be able to change things for the better eventually.
Many thanks for your help and comments, I really do value all of them.