SQL Get number of commas in a string

@ewangoya  unfortunately that would not work consistently...
SELECT @count = LEN(@string) - LEN(REPLACE(@string, ',', ''))


e.g consider string 'a, , ,'

replacing the commas with space / emopty string would lead LEN() to report it as length 1 not the 3 you were hoping for
you could use DATALENGTH and replace with an empty string

if Varchar
SELECT @count = DATALENGTH(@string) - DATALENGTH(REPLACE(@string, ',', ''))
if NVarchar
SELECT @count = (DATALENGTH(@string) - DATALENGTH(REPLACE(@string, ',', '')))/2

Lowfatspread - Not sure on why did you tell that won't work.
I tried ewangoya's query for string 'a,,,' and got the count as 3.

'a    ,       ,        ,'


try it with spaces  between the comma at the end of the string.....

LEN ignores spaces   so if you remove the last/trailing comma you reduce the reported LEN() value by more than 1....

use datalength()  for consistency and cater for bytes/characters depending on the datatype.

Thanks.. this will ALWAYS be an address so some different scenerios would be like



5222 michaux rd GSO, NC,27410

or

5222 michaux rd GSO NC 27410

The real point of the query above is to parse the delimited string into a table, but using count you can also derive the comma count.

Thanks!