Using T-SQL to Count Corporate Partners

My company provides financial data about more than 10,000 US companies that are listed on different US stock exchanges. One of our clients asked me to develop a screening tool that would let their users analyze companies based on market cap, stock price, phase of product development, years in cash, number of corporate partners, and other factors.

I encountered a problem when I tried to display the count of corporate partners for each company. The data in the CorporatePartner column is a comma-delimited string that isn't in a relational database format. To display the count, I tried using dynamic SQL, which worked until I put in a WHERE condition to check the user-selected criteria—No of Partners<3 (or) No of Partners>3.

To find the number of corporate partners for each company, I wrote the T-SQL query that Listing 7 shows. This query logic works wonderfully. I can use a SELECT statement to display the count of corporate partners, and I can use the same logic in the WHERE clause to check the count based on user-selected criteria. This query subtracts the derived length (by replacing all commas with an empty string) from the total length of each corporate partner. The code in Listing 8 includes the sample data that I used to populate the tblCompanyInfo table, which consists of company ID, symbol, and a list of each company's corporate partners.

—Harinatha Reddy Gorla
[email protected]