Android 如何在 SQLite 中拆分逗号分隔值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24258878/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to split comma-separated value in SQLite?
提问by dipali
I want to split comma-separated Stringinside SQLite database
我想String在 SQLite 数据库中拆分逗号分隔
Example: I have a Categorycolumn in 1 of my table.
示例：Category我的表的 1 中有一个列。
|Category |
|------------------------------|
|Auto,A,1234444 |
|Auto,B,2345444 |
|Electronincs,Computer,33443434|
I want to get only a single value from above string.
我只想从上面的字符串中获取一个值。
value1: Auto
value2: A
value3: 1234444
I have searched a lot on Google; I found a way to replace comma using Replace()and Trim(). However, I want to get an easier approach.
我在谷歌上搜索了很多；我找到了一种使用Replace()and替换逗号的方法Trim()。但是，我想获得一种更简单的方法。
In SQL, there is provide SubString(). But in SQLite, there is no such function. How to solve it?
在 SQL 中，有提供SubString(). 但是在 SQLite 中，没有这样的功能。如何解决？
EDIT: I have checked substr(). But, this function can be set maximum length to get the string value, and my string value doesn't have fixed length.
编辑：我已经检查过substr()。但是，这个函数可以设置最大长度来获取字符串值，而我的字符串值没有固定长度。
采纳答案by Harish Patel
please create this function in your sqllite and pass 2 argument first is seprator and second string.
请在您的 sqllite 中创建此函数，并首先传递 2 个参数是分隔符和第二个字符串。
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
)
回答by user1461607
You can use a common table expression to split comma separated values in SQLite.
您可以使用公用表表达式在 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, instr(str, ',')),
substr(str, instr(str, ',')+1)
FROM split WHERE str!=''
) SELECT word FROM split WHERE word!='';
Output is as expected:
输出如预期：
Auto
A
1234444
回答by peak
This variation of the answer provided by @user1461607 ensures that any CSV values that happen to be empty strings are preserved:
@user1461607 提供的答案的这种变体确保保留任何碰巧是空字符串的 CSV 值：
WITH RECURSIVE split(value, str) AS (
SELECT null, 'Auto,A,1234444' || ',' -- the string to be split
UNION ALL
SELECT
substr(str, 0, instr(str, ',')),
substr(str, instr(str, ',')+1)
FROM split WHERE str!=''
) SELECT value FROM split WHERE value is not NULL;
Converting a CSV line to a JSON array
将 CSV 行转换为 JSON 数组
Assuming the JSON1 extension has been loaded, you could use json_group_array(value)in the last line to convert
the CSV to a JSON array of strings.
假设已加载 JSON1 扩展，您可以json_group_array(value)在最后一行中使用将 CSV 转换为字符串的 JSON 数组。
回答by Ron Burk
I like the answer from @user1461607 except: it seems to me the SQLite documentation warns against assuming any particular order from a SELECT, both in the general case, and in the specific case of a recursive SELECT. Here, I modified that answer to add an ordering column in a manner that I thinkSQLite guarantees to work.
我喜欢@user1461607 的答案，除了：在我看来，SQLite 文档警告不要假设来自 SELECT 的任何特定顺序，无论是在一般情况下，还是在递归 SELECT 的特定情况下。在这里，我修改了该答案，以我认为SQLite 保证工作的方式添加了一个排序列。
I also cosmetically changed the example from a comma-separated list to a path, to suggest there are cases where you really need to process things in a particular order. This example also prints out all the columns from the temporary table so it's slightly easier to see what went on. AFAICT, a CTE in SQLite does not have the usual ROWID column, so it seems like adding some ordering column yourself really is required to sleep soundly at night.
我还将示例从逗号分隔列表更改为路径，以表明在某些情况下您确实需要按特定顺序处理事物。此示例还打印出临时表中的所有列，以便更容易查看发生了什么。AFAICT，SQLite 中的 CTE 没有通常的 ROWID 列，因此似乎确实需要自己添加一些排序列才能在晚上睡个好觉。
WITH RECURSIVE split(seq, word, str) AS (
SELECT 0, '/', 'home/ronburk/layers/branch'||'/'
UNION ALL SELECT
seq+1,
substr(str, 0, instr(str, '/')),
substr(str, instr(str, '/')+1)
FROM split WHERE str != ''
) SELECT * FROM split ORDER BY split.seq ASC;
