Knowledge Drop

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

  • 15 June 2021
  • 1 reply

Userlevel 5

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:




This content is subject to limited support.                




1 reply