which command in sqlite separates a string into columns?

oracle =>

select regexp_substr('2;xxxxxxx;2361295;2775371;345640;2638;yyyyyyyyy;1084;fffffff 1;28/12/2021;28/12/2021;', '^misref+', 1, 3) from dual

result: 2361295

SQlite =>

This command (regexp_substr) does not work on SQLite. I need it to be in columns and not rows.

1. ^{^} Misreference

In this thread can be found a few examples of Gunter's suggestion.

Thanks for the answer, but could you give me a simpler example?

Thanks for the answer, but could you give me a simpler example?

There is no simpler answer, yet, that I'm aware of. The recursive CTE's in the thread I linked can be readily adapted to your problem, but some SQL crafting will be required. Or, as Gunter suggested, a "split" extension, providing a table-valued result, would be doable. But that has not been done and made known here, to my knowledge.

With luck, some other forum participant will write that SQL for you. But do not count on that.

You can find examples here.

There you can find all examples related to your use case, from simplest one and up to solving sudoku.

If it is just one time operation, then I really can't see a reason to compile own extension, it is so trivial task for any external tools, like an awk for example that available on any platforms

#!/bin/sh

### creating test database
echo "
begin transaction;
create table t ( str text );
insert into t (str) values ('2;xxxxxxx;2361295;2775371;345640;2638;yyyyyyyyy;1084;fffffff 1;28/12/2021;28/12/2021;');
insert into t (str) values ('2;xxxxxxx;2361296;2775371;345640;2638;yyyyyyyyy;1084;fffffff 1;28/12/2021;28/12/2021;');
insert into t (str) values ('2;xxxxxxx;2361297;2775371;345640;2638;yyyyyyyyy;1084;fffffff 1;28/12/2021;28/12/2021;');
commit;
" | sqlite3 plinio.db


### extraction of 3rd field
echo 'select str from t;' | sqlite3 plinio.db | awk -F\; '{print $3}'

Here's one that's maybe simpler and with different versions (read the whole thread) that splits on spaces (but you can change those to commas or semicolons easily):

forum post 840c98a8e87c2207?t

It does split it into rows though. There is no way in any SQL engine (SQLite or otherwise) to split text into output columns, as you seem to be requesting. All output columns, for any query, have to be known at the time of parsing.

Hope that helps.

Thanks for the reply, but it gives the message that this function does not exist in sqlite.

select split_part(s_recado,';',2) from tp_recados

2.6 Message : no such function:_split_part

Did you load the extension?

Could you tell me the correct extension?

Sqlite expert - Personal Edition Version 3.5.76.2496

Could you tell me the correct extension?

The message you responded to when you wrote:

Thanks for the reply, but it gives the message that this function does not exist in sqlite

Has a link to the extension, its C file, and instructions for how to load it.

Another idea: use the json1 module (built-in in sqlite cli) to create a JSON-array with some tiny string-tinkering:

• json_quote the string
• replace all ; with ","
• embed in [ ]

sample data as of one of the other suggestions here:

create table t ( str text );
insert into t (str) values ('2;xxxxxxx;2361295;2775371;345640;2638;yyyyyyyyy;1084;fffffff 1;28/12/2021;28/12/2021;');
insert into t (str) values ('2;xxxxxxx;2361296;2775371;345640;2638;yyyyyyyyy;1084;fffffff 1;28/12/2021;28/12/2021;');
insert into t (str) values ('2;xxxxxxx;2361297;2775371;345640;2638;yyyyyyyyy;1084;fffffff 1;28/12/2021;28/12/2021;');

split all

> select key, value
> from t, json_each( '['||replace(json_quote(str),';','","')||']' );

0|2
1|xxxxxxx
2|2361295
3|2775371
4|345640
5|2638
6|yyyyyyyyy
...

or just each 3rd entry

> select value
> from t, json_each( '['||replace(json_quote(str),';','","')||']' )
> where key=2;

2361295
2361296
2361297

Should work with other separators too by changing the replace(...,';',...) accordingly. And with fairly pathetic strings containing quotes and more as well thanks to the json_quote.