SELECT all substrings (n-grams) of length n?

Question

How could I select all n-grams, ie. substrings of length n from a string using SQL? For example, the 3-grams of string example are exa, xam, amp, mpl, ple.

I'm using PostgreSQL to be more precise.

Answer 1

This does the trick:

SELECT SUBSTRING('example' FROM n FOR 3) 
FROM GENERATE_SERIES(1, LENGTH( 'example' )-2, 1) n;
exa
xam
amp
mpl
ple

Here it is in a function:

CREATE OR REPLACE FUNCTION ngrams(varchar, integer) RETURNS SETOF TEXT AS $$
SELECT SUBSTRING($1 FROM n FOR $2)::TEXT
FROM GENERATE_SERIES(1, LENGTH($1)-($2-1), 1) n;
$$ LANGUAGE SQL;

Use it like:

SELECT ngrams('example', 3)

Answer 2

There is a module in PostgreSQL which supports trigrams (not n-grams). it is called pg_trgm.

Do the following:

CREATE EXTENSION pg_trgm;

then ...

SELECT show_trgm('hello');

The result will be

 {"  h"," he",ell,hel,llo,"lo "}

Answer 3

DECLARE @str varchar(MAX)
DECLARE @ngram int 
DECLARE @i int 

SET @i = 1
SET @ngram = 5 
SET @str = 'Helloworldsed'

WHILE (@i <= (len(@str)-@ngram)+1)
BEGIN
SELECT SUBSTRING(@str,@i,@ngram)
SET @i = @i + 1
END