Last active
December 3, 2022 19:35
-
-
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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