-- Creating q-grams as tokens

-- Create an auxiliary relation N
-- It contains integers from 1 to @UPPERLIMIT

DROP TABLE [dbo].[N]
GO
CREATE TABLE [dbo].[N] (
[i] int PRIMARY KEY
)

-- Using the WHILE-loop, populate the relation
DECLARE @I int
DECLARE @UPPERLIMIT int
SET @I=1
SET @UPPERLIMIT=100
WHILE @I <= @UPPERLIMIT
	BEGIN
	INSERT INTO [dbo].[N] VALUES(@I)
	SET @I = @I+1
	END
GO

-- Create the tables where we store the tokens
-- Be careful to allow enough characters to store
-- the tokens. Below we will use 3-grams, so
-- char(3) is ok.
CREATE TABLE [dbo].[R1Tokens] (
[tid] int NOT NULL,
[token] char(3) NOT NULL,
FOREIGN KEY (tid) REFERENCES [R1](tid)
)

CREATE TABLE [dbo].[R2Tokens] (
[tid] int NOT NULL,
[token] char(3) NOT NULL,
FOREIGN KEY (tid) REFERENCES [R2](tid)
)

GO

-- Populate the tokens relations by creating q-grams
DECLARE @Q int
SET @Q=3
INSERT INTO R1Tokens

SELECT R1.tid, SUBSTRING(SUBSTRING('$$$$$$$',1,@Q-1) + UPPER(R1.str) + SUBSTRING('$$$$$$$',1,@Q-1), N.i, @Q) AS token
FROM N INNER JOIN R1 ON N.i <= LEN(R1.str) + (@Q-1)

INSERT INTO R2Tokens
SELECT R2.tid,SUBSTRING(SUBSTRING('$$$$$$$',1,@Q-1) + UPPER(R2.str) + SUBSTRING('$$$$$$$',1,@Q-1), N.i, @Q) AS token
FROM N INNER JOIN R2 ON N.i <= LEN(R2.str) + (@Q-1)

GO