CREATE TABLE Master ; (Pkey I PRIMARY KEY, ; Skey c(10) CHECK Master_Skey_Validator()) CREATE TABLE Suffix ; (Skey c(10), ; Pkey I, ; FOREIGN KEY Pkey TAG Pkey REFERENCES test1) INDEX ON skey TAG skeyWith Master_Skey_Validator() in the database as a stored procedure:
FUNCTION Master_Skey_Validator LOCAL lnPrimaryKey, lcSearchKey, i lnPrimaryKey = Master.Pkey DELETE FROM Suffix WHERE pkey = lnPrimaryKey lcSearchKey = ALLTRIM(Master.Skey) FOR i = 1 TO LEN(lcSearchKey) INSERT INTO Suffix (Pkey, Skey) ; VALUES (lnPrimaryKey, SUBSTR(lcSearchKey, i)) ENDFOR ENDFUNCYou can search with this select:
SELECT DISTINCT Master.* ; FROM Master INNER JOIN Suffix ON Master.Pkey = Suffix.Pkey ; WHERE Suffix.skey = "4-5"