Count Number of Pdf Files

Recently I had been tasked with having to count the number of Pdf Filenames within a table. The challenge was that the column can have multiple filenames within a column separated by a commas.

My solution was to create a case statement within the query for the count in a row column and then sum it up to get the total.

Sample Code:

CREATE TABLE #PdfFiles
(
	Files VARCHAR(2000)
)

insert into #PdfFiles (Files) values ('file1.pdf, file2.pdf, file3.pdf')
insert into #PdfFiles (Files) values ('file4.pdf')
insert into #PdfFiles (Files) values ('file5.pdf, file6.pdf')
insert into #PdfFiles (Files) values ('file7.pdf')
insert into #PdfFiles (Files) values ('file8.pdf,file9.pdf,file10.pdf')

select * from #PdfFiles
select		sum(case when ltrim(rtrim(isnull(Files, ''))) = '' 
			then 0 
			else len(Files) - len(replace(Files, ',', '')) + 1  
		end) as NumPdfs
from #PdfFiles

drop table #PdfFiles