Skip to content

Instantly share code, notes, and snippets.

@dekassegui
Last active December 3, 2022 19:35
Show Gist options
  • Save dekassegui/37ed82e52fa84de6d9c08da0d4a6eb92 to your computer and use it in GitHub Desktop.
Save dekassegui/37ed82e52fa84de6d9c08da0d4a6eb92 to your computer and use it in GitHub Desktop.
SQlite only -- snippet to demonstrate how to SPLIT STRING in substrings separated with custom separators.
with separators as ( values (' '), (','), ('-'), ('.') ),
source (s) as ( select " Will, thought and action." ),
bag (q) as ( -- POSITIONS OF ALL SEPARATORS
with dim (len) as ( select length(s) from source ),
ndx (n) as (
select 1 union all select n+1 from ndx, dim where n < len
) select 0 --> PSEUDO SEPARATOR IN FRONT OF SOURCE STRING
union all select n from ndx, source where substr(s, n, 1) in separators
union all select len+1 from dim --> PSEUDO SEPARATOR AT BOTTOM
),
pre (p) as ( -- POSITIONS OF SEPARATORS PRECEDING NON SEPARATORS
select q from bag where not q+1 in bag
) select printf("%2d %2d <%s>", p, z, substr(s, p+1, z-p-1)) from (
select p, (select min(q) from bag where q > p) as z from pre where z
), source;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment