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

问题描述

我要拆分逗号分隔的字符串内部SQLite数据库

I want to split comma-separated String inside SQLite database

例如：我有一个类别列1我的表

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

我已经搜索了很多关于谷歌;我发现了一种使用替换以替换逗号（）和修剪（）。不过，我希望得到一个更简单的方法。

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中，有提供子串（）。但是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.

推荐答案

请在你的sqllite创建此功能，并通过2的说法首先是seprator和第二个字符串。

please create this function in your sqllite and pass 2 argument first is seprator and second string.

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
)

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