Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Count Instances based on specific string

Author  Topic 

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2013-05-08 : 13:16:42
I have a column which contains the following values

colA
A
ABC, DEF
GHI, J
KLM, NOP, QRS, TUV

I want to count the number of times a value appears before and after a comma.

My desired result would be:


colA colB
A 1
ABC, DEF 2
GHI, J 2
KLM, NOP, QRS, TUV 4


Any help would be appreciated.....

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-08 : 13:24:14
[code]SELECT ColA,
LEN(colA) - LEN(REPLACE(colA,',','')) + 1 AS ColB
FROM
YourTable;[/code]If there is the possibility that there are leading and/or trailing comma's or if there are groups of comma's with nothing in between, and if you want to account for all of that, there is a little bit more work required.
Go to Top of Page

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2013-05-08 : 13:30:55
Nice, that works great!
Go to Top of Page
   

- Advertisement -