SQL Porosity - CSV Element Calculation
Background: Recently "walked" into a scenario where a client was storing an variable sized CSV string in a field. This field held data that would eventually be evaluated as a "pseudo" entity for reporting purposes. Problem Due to the need for performance and reliability, the normal practice of splitting the string into a result set and then performing a SQL Count on the result set, would be overly complex and may cause performance issues as the length is variable (i.e. varchar(max) ) Solution After reviewing several search queries, which most ended up at my old faithful Stack Overflow . I came across an approach that is nothing more than Porosity . For my purpose, it fit the bill very cleanly without overly transitioning a single value into a data-set and then calculating the data-set. Net Result Data-Set : Source: SQL Data - Source Value SQL Data - Comma's Voided Math n - Len([Field]) m - Len(Replace([Field], ',', '