Count numbers separated (delimited) by comma in SQL Server

Firuz

SOLVED CLOSED ACTIVE ANSWERED
User: Firuz
Posted: on Dec 04, 2015 01:51 AM
Forum: SQL Server
Answer: 1
Views: 2045

Hi! I have table like this.

ID	FlatNumber
1	1,2,3,4
2	2
3	3
4	5,6,7,8
5	9
6	10
7	11
8	12
9	13

I used this query for count number flat.

Select LEN(FlatNumber) - LEN(REPLACE(FlatNumber,',', ''))+1 Result from Flats

But got this result:

ID	FlatNumber
1	4
2	1
3	1
4	4
5	1
6	1
7	1
8	1
9	1

Now I want count number of result. For example (4+1+1+4+1+1+1+1+1=19). How I can count this?

Download FREE API for Word, Excel and PDF in ASP.Net

Shashika...

0 1

Reply
ANSWER
User: Shashikant
Replied: on Dec 04, 2015 01:52 AM
Report

Here I have created SQL Script.that will help you out.

SQL

DECLARE @tblFlats AS TABLE(Id INT,FlatNumber VARCHAR(100))

INSERT INTO @tblFlats VALUES
(1,'1,2,3,4'),
(2,'2'),
(3,'3'),
(4,'5,6,7,8'),
(5,'9'),
(6,'10'),
(7,'11'),
(8,'12'),
(9,'13')

Select SUM(LEN(FlatNumber) - LEN(REPLACE(FlatNumber,',', ''))+1) Result from @tblFlats

Output:

Result 15

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Count numbers separated (delimited) by comma in SQL Server

Company

Explore

Follow us

Contact Us