如何在 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屋!