How to split comma separated value in SQLite?

Question

I want to separate comma-separated String inside SQLite database

Example: I have a Category column in 1 of my table.

 |Category | |------------------------------| |Auto,A,1234444 | |Auto,B,2345444 | |Electronincs,Computer,33443434| 

I want to get only one value from the line above.

 value1: Auto value2: A value3: 1234444 

I searched a lot on Google; I found a way to replace the comma using Replace() and Trim() . However, I want a lighter approach.

In SQL, there is SubString() . But there is no such function in SQLite. How to solve it?

EDIT . I checked substr() . But for this function, you can set the maximum length to get a string value, and my string value does not have a fixed length.

Answer 1

please create this function in your sqllite and pass argument 2 first - the separator and the second line.

 CREATE FUNCTION [dbo].[Split] ( @Sep char(1) , @S varchar(512) ) RETURNS TABLE AS RETURN ( WITH Pieces(pn, start, stop) AS ( SELECT 1, 1, CHARINDEX(@Sep, @S) UNION ALL SELECT pn + 1, stop + 1, CHARINDEX(@Sep, @S, stop + 1) FROM Pieces WHERE stop > 0 ) SELECT pn, SUBSTR(@S, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS S FROM Pieces ) 

Answer 2

You can use a common table expression to separate values ​​with a comma in SQLite.

 WITH split(word, str) AS ( -- alternatively put your query here -- SELECT '', category||',' FROM categories SELECT '', 'Auto,A,1234444'||',' UNION ALL SELECT substr(str, 0, coalesce(instr(str, ','), length(str))), substr(str, instr(str, ',')+1) FROM split WHERE str!='' ) SELECT word FROM split WHERE word!=''; 

The output is as expected:

 Auto A 1234444