linux 拆分字符串函数,字符串拆分类似awk

在Linux中，如果想将一个字符串，例如“aaa,bbb,ccc,ddd”以逗号拆分，得到其中的某个值如“ccc”，可以使用如下命令：echo "aaa,bbb,ccc,ddd" | awk -F, '{print $3}'，这样的功能在SQL中也同样可以实现，参考以下表函数：

CREATE FUNCTION fawk(

func_serch char(1),

func_chars varchar(2048)

)

RETURNS table(

id integer,

values varchar(128)

)

SPECIFIC fawk

LANGUAGE SQL

CONTAINS SQL

RETURN

WITH temp(pvalue, plocate, nplocate) as(

SELECT substr(func_chars, 1, nplocate -1),

nplocate,

nplocate

FROM TABLE(VALUES (locate(func_serch, func_chars))) t(nplocate)

WHERE nplocate > 0

UNION ALL

SELECT (case when nplocate > 1 then substr(pvalue, 1, nplocate - 1) else substr(pvalue, 1) end),

plocate + nplocate,

nplocate

FROM (

SELECT pvalue,

plocate,

nplocate pplocate,

locate(func_serch, pvalue) nplocate

FROM (

SELECT (CASE WHEN plocate + 1 <= length(func_chars) THEN substr(func_chars, plocate + 1) ELSE '' END) pvalue,

plocate,

nplocate

FROM temp

) t

) t

WHERE nplocate > 0 OR (nplocate = 0 AND pplocate <> 0)

) SELECT rownumber() over() id,

pvalue

FROM temp

ORDER BY plocate

@

执行查询：select values from table(fawk(',', 'aaa,bbb,ccc,ddd')) t where id = 3