3

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.

4

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)
|improve this answer|||||
4

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 "}
|improve this answer|||||
-2
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
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.