7

Say I have a table where Col2 is varchar

Col1 Col2
1    001,002
2    003,004,005

I need to count the number of elements in Col2,and return it, if I do:

select --do something here with column-- from table

it'll give me:

2
3
Ben
  • 51,770
  • 36
  • 127
  • 149
user2846737
  • 201
  • 3
  • 5
  • 13

3 Answers3

21

So by counting the number of ,s you have in Col2 and adding 1 to it would give you your answer. Below I get the length of Col2. Then I replace the ,s with nothing and get that length. I take the first length and subtract the second length to get the total number of commas. Then simply add 1 to the result to get the total you are looking for:

SELECT (LENGTH(Col2) - LENGTH(REPLACE(Col2,",","")) + 1) AS MyCol2Count
FROM MyTable
Linger
  • 14,942
  • 23
  • 52
  • 79
  • Used this, got ```ERROR: function length(jsonb) does not exist LINE 1: SELECT (LENGTH(recipients) - LENGTH(REPLACE(recipients,","))... ``` Rookie q but does Postgres not support Length? – Joshua Dance Nov 10 '16 at 22:16
  • 2
    Yes PostgreSQL does support the length function. See [**String Functions and Operators**](https://www.postgresql.org/docs/8.1/static/functions-string.html). Take a look at this [**SQL Fiddle**](http://sqlfiddle.com/#!15/96ae9/5). You need to use the single quotes in Postgre and not the double quotes I show in the answer. – Linger Nov 11 '16 at 13:01
  • Whats changed needs to be done to use it in MSSQL ? – Duffer Mar 20 '19 at 01:01
  • Got the thing to work in MSSQL using `LEN(Col2)-LEN(REPLACE(col2,';',''))` – Duffer Mar 20 '19 at 01:48
14

If it's always formatted like that simply count the number of commas and then add 1:

select regexp_count(col, ',') + 1
  from table
Ben
  • 51,770
  • 36
  • 127
  • 149
2

Linger's answer is incorrect in the special case that Col2 is empty. Instead of yielding a count of 0 you'd get an incorrect count of 1. You can account for this special case with a CASE statement as follows:

SELECT CASE WHEN Col2='' THEN 0 ELSE LENGTH(Col2)-LENGTH(REPLACE(Col2,",",""))+1 END AS MyCol2Count
FROM MyTable

Edit: As William has pointed out the empty string WHEN test may be inaccurate if your table is setup to allow NULLs for the column in question. In such a case you'd need to replace the Col2='' test with Col2 IS NULL (at least in SQL Server).

Note: Apologies, I would have put this as a comment on Linger's answer but I'm not allowed to comment yet.

Mod Bern
  • 21
  • 3