Knowledge Drop

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

  • 15 June 2021
  • 0 replies
  • 2088 views

Userlevel 4

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:

80dd48d1-f99e-402c-84cb-075eb52da408

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

 

This content is subject to limited support.                

 

 

 


0 replies

Be the first to reply!

Reply