CODEWITHSUNDEEP
sql-server
user2781230
user2781230

Reputation: 21

Find total no of items in a cell?

How can I find out total number of items in a particular cell?

In a table like:

eno   ename
1      hari,giri,sathish
2      naresh,suresh
3      sathish

I want an output like this:

eno    ename
1       3
2       2
3       1

How can it achieve?

Upvotes: 2

Views: 45

Answers (2)

Rahul Tripathi
Rahul Tripathi

Reputation: 172428

Try this:-

Select len(ename) - len(replace(ename, ',', ''))+1 as Employee
From Table name

DEMO SQL FIDDLE

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269713

You can use this trick

select eno,
       1 + len(ename) - len(replace(ename, ',', '')) as ename
. . .

That is, count the number of commas and add 1. You can count the number of commas by taking the length of the string and subtracting the length without commas.

Upvotes: 2

Related Questions