SQL Pattern: How can I count the number of comma separated values in a string?

[LookerExperts]

Knowledge Drop

Last tested: Aug 16, 2017
 

This might not be a common request, but the method to do so is below:

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

FROM MyTable

Basically, you replace all occurrences of , with an empty string "", then subtract its LENGTH from the LENGTH of the unadulterated string, which gives you the number of , characters. This doesn't tell you the number of comma separates values, however. If you have four comma separated values, there will be three commas. In order to get the number of comma separated values, simply add 1.

An example in Impala syntax is below:

Source: https://stackoverflow.com/questions/19915746/count-the-number-of-elements-in-a-comma-separated-strin...

This content is subject to limited support.                

[CaseyR]

Nice!