Searching non-adjacent Varbinaries in another Varbinary

[stepen123]

I need to search between 1 and 5 NON-ADJACENT occurrences of a Varbinary field in another Varbinary field.
For example to find in which record are A,B,C and D, querying a field with

stuffAstuffstuffBstuffCstuffDstuffstuff.

Here is the real picture:

There is a Table1 (no more than 300,000 records) with a Varbinary Field1(4), created by

REPLACE ALL Field1 WITH BINTOC(RECNO()) e.g.0h80001778.

There is another Table2 (less than 600,000 records), with a Varbinary Field2 (100), where I hold several sequences of Field1, created by the concatenation of Table1.Field1,

e.g. 8000287A 80001EF9 80001E54 8000082D 800038A3 800012F4 (there are no spaces in the actual field).

I need to use ? as a wildcard char in order to find non-adjacent occurrences of Field1 in Field2, e.g.

80001EF9, 8000082D and 800012F4 (the second, the fourth and the sixth entry).

LIKE () would be the perfect solution, but unfortunately it does not support the Varbinary data type.

I’ve chosen to work with Varbinary because
- It can hold numbers above 2G in only 4 bytes,
- Unlike Integer it can create sequences AND

Question 1: Is there a better solution for keeping sequences of numbers in a field than Varbinary?

Question 2: How can I search non-adjacent Q values in a Q string?

 

[MrDataGuy]

Stepen123
To be honest I do not really follow your question (I admit that I may not be the brightest blub in the package.) but maybe I can make some comments that you will find in some small way helpful.

“It can hold numbers above 2G in only 4 bytes” Is space really such an issue with the application? I would want to work with something easy before worrying about field size too much. And IMO 300,000 records is not a large table (unless it has lots of columns that are big like 250 character fields )

Might having a sub/detail table be a better solution? That is create a table that has a structure like Prime_Key, Foreign_Key_To_Main_Table, Value, Order (if needed) (The field names I would use might be: PK(int), Table1_FK (int), Value (character-7), Order (int) )

Now one could do normal SQL to find the wanted information.

Does this help at all?

OH! One other comment In 20+ years of xBase programming I have never used the VarBinary data type (Maybe I should be but that is another question) so it seems like one under most situation one does not need to use it.


Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[JRB-Bldr]

"I need to use ? as a wildcard char "

First of all there are NO 'wildcards' in a VFP Search.

You own code can allow the user to enter whatever character(s) you want, and then your code can build its own query using valid VFP syntax, but 'wildcards' are not part of that.

Something like:

* --- User entered: ---
  cFind1 = '?4567?'
  cFind2 = '?4321?'
  cFind3 = ''

* --- Your code changes user entry into VFP Query syntax ---
cSearchString1 = STRTRAN(cFind1,'?','')
cSearchString2 = STRTRAN(cFind2,'?','')
cSearchString3 = STRTRAN(cFind3,'?','')

SELECT *;
 FROM MyTable;
 WHERE IIF(!EMPTY(cSearchString1),cSearchString1 $ Field1,.F.);
 OR  IIF(!EMPTY(cSearchString2),cSearchString2 $ Field2,.F.);
 OR  IIF(!EMPTY(cSearchString3),cSearchString3 $ Field3,.F.)
 INTO CURSOR Results

"Is there a better solution for keeping sequences of numbers"

I don't know about BETTER, but another alternative would be a Character field type. It can be up to 254 characters in length (but you certainly could get by with far fewer), which, if populated with numeric values, could represent a LARGE number positive and/or negative.

And your own code could be created so as to guarantee 'sequential' values.

Lastly, as MrDataGuy says above, you might want to consider reviewing and/or changing your data table architecture to achieve better normalization of the data - thereby simplifying your searches, etc.

Good Luck,
JRB-Bldr

 

[stepen123]

Thank you, MrDataGuy and jrbbldr for your consideration. I should have explained beforehand that I am not using VFP for a business or office application, so the traditional database structure is not applicable in my case. I really need to keep on record sequences of numbers (record IDs). Table1 contains records, and each time a record has been accessed, its ID is stored in one record in Table2. On some condition this concatenation is stopped and another record in Table2 is APPENDed and the loop starts again.

@ jrbbldr:

another alternative would be a Character field type.

This one may solve my problem. Would you explain how to populate a Char field with numbers from 1 to 300,000 (each entry will represent a record in Table1)?

 

[MrDataGuy]

how to populate a Char field with numbers from 1 to 300,000"

A simple Character field can not be used since the largest amount of data a single character field can hold is 254 (or is that 255?) characters. So you could hold maybe 1 to 120 ( or so ) numbers. Now one could store them in a memo field thou. (I really do not think one should do that but...)

Untested code to follow:

m.text = []
For I = 1 to 300,000
  m.text = m.text + Alltrim( str( I ) ) + chr(13)
Endfor 

Replace Table.Memo_Field with m.Text

Note the above code will put one number on a line, if you want them to be bunched up (12345678901112131415 etc.) then drop the chr(13)
------------------------------
"I should have explained beforehand that I am not using VFP for a business or office application, so the traditional database structure is not applicable in my case"

Not clear what you are trying to say here. If you are saying that you are 'given' the data structure and you think that you have to live with it, then just create a blank table(s) and copy/translate/break part the information as given and place the data in a better design set of tables. Do not live with the structure given if it is a poor design.
----------------
" Table1 contains records, and each time a record has been accessed, its ID is stored in one record in Table2. On some condition this concatenation is stopped and another record in Table2 is APPENDed and the loop starts again."

Can the "condition" be adjusted so that EVERY time a new Table 2 Record is added? Is so then my orginal idea in my first posting can be used. e.g a Primary key, foriegn key to table one etc. Keys being integers if numbers are always used.

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[stepen123]

Dear Mr. Testi,

Thank you for your reply. I will experiment with your suggestion to reform the database. Meanwhile I would like to know how to store positive integers by 2,3 or 4 characters.

I need a way to convert either a group of
- 2 characters into numbers 1 - 65535 or
- 3 characters into numbers 1 - 16,777,215 or
- 4 characters into numbers 1 - 4,294,967,295

Does anyone know how I could do it? Is it possible at all?

 

[MrDataGuy]

I need to get off to bed, but here is a clue for you:

c1 = "a"
c2 = "%"
c3 = "Q"
c4 = Chr(254)

Code2 = c1 + ( c2 * 256 )
Code3 = c1 + ( c2 * 256 ) + ( c3 * 256 * 256 ) 
Code4 = c1 + ( c2 * 256 ) + ( c3 * 256 * 256 ) + ( c4 * 256 * 256 * 256 )

BTW 256 * 256 is 65,536 (not 65,535 so maybe you wanted 0 to 65,535 )

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[stepen123]

This solves my problems.
Thank you a lot.

 

[Olaf Doschke]

Normal integers can create sequences via autoinc. For what case do you need values greater than 2^32-1? The 2GB limit practically makes it impossible to even get close to 2GB records, so you never need more than 2^32 values for integer IDs.

Are you emulating a 1:n or even an n:m relationship by storing keys into a large Q(100) field and then query for relations via an "instring" search of IDs?

@jrbbldr, of course there is a search for the '?' wildcard via LIKE in SQL-SELECTS, but as stepen knows himself, LIKE cannot be done on Q() fields.

Bye, Olaf.