如何在 SQLite 中拆分逗号分隔值? [英] How to split comma-separated value in SQLite?

问题描述

我想在 SQLite 数据库中拆分逗号分隔的 String

I want to split comma-separated String inside SQLite database

示例:我的表 1 中有一个 Category 列.

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 a single value from above string.

value1: Auto
value2: A
value3: 1234444

我在谷歌上搜索了很多；我找到了一种使用 Replace() 和 Trim() 替换逗号的方法.但是，我想采用更简单的方法.

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.

在 SQL 中，提供了 SubString().但是在 SQLite 中，没有这样的功能.如何解决?

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

编辑:我已经检查了 substr().但是，这个函数可以设置最大长度来获取字符串值，而我的字符串值没有固定长度.

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.

推荐答案

SQLite 中可以使用公用表表达式来分割逗号分隔值.

You can use a common table expression to split comma separated values 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, instr(str, ',')),
    substr(str, instr(str, ',')+1)
    FROM split WHERE str!=''
) SELECT word FROM split WHERE word!='';

输出符合预期:

Auto
A
1234444

这篇关于如何在 SQLite 中拆分逗号分隔值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！