计算Oracle中逗号分隔的字符串中的元素数 [英] Count the number of elements in a comma separated string in Oracle

问题描述

说我有一个表，其中Col2是varchar

Say I have a table where Col2 is varchar

Col1 Col2
1    001,002
2    003,004,005

如果需要，我需要计算Col2中的元素数量并返回:

I need to count the number of elements in Col2,and return it, if I do:

select --do something here with column-- from table

它会给我:

2
3 

推荐答案

因此，通过计算Col2中,的数量并将其加1即可得到答案.下面我得到Col2的长度.然后，我将,替换为空并得到该长度.我采用第一个长度，然后减去第二个长度，以得到逗号的总数.然后只需将1加到结果中即可得到您想要的总数:

So by counting the number of ,s you have in Col2 and adding 1 to it would give you your answer. Below I get the length of Col2. Then I replace the ,s with nothing and get that length. I take the first length and subtract the second length to get the total number of commas. Then simply add 1 to the result to get the total you are looking for:

SELECT (LENGTH(Col2) - LENGTH(REPLACE(Col2,",","")) + 1) AS MyCol2Count
FROM MyTable

这篇关于计算Oracle中逗号分隔的字符串中的元素数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！