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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityHow 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.
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)
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 "}
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