Find and Count all records containing particular character (Comma character) in SQL Server

PRA

SOLVED CLOSED ACTIVE ANSWERED
User: PRA
Posted: on Oct 26, 2017 06:27 AM
Forum: SQL Server
Answer: 1
Views: 3217

Hi! I want count house and flat sum in Sql query. When house and flat not null then count flat not count house else count house not count flat, because flat hasn’t any number.

Id	House	Flat	Sign
1	1	1,2	1
2	1	39,40	1
3	5		1
4	6		1
5	7	5	1
6	7	6	1
7	8	10,11	1
8	8	9	1
9	9,10		1

Output result must be 13.

I want count number house and flat from two column. If house column has number and flat column has number then get flat number non count house column number. If house column has number and flat column is free then count house number. Like below, but here count only one column. Now I want count from two column.

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

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

kalpesh

0 3

Reply
ANSWER
User: kalpesh
Replied: on Oct 26, 2017 07:12 AM
Modified: on Oct 26, 2017 07:16 AM
Report

Refer the below sample test query for your reference.

SQL

DECLARE @tblFlats AS TABLE(Id INT,House VARCHAr(100),FlatNumber VARCHAR(100),[sign] INT)
	
INSERT INTO @tblFlats VALUES
(1,'1','1,2'  ,1),
(2,'1','39,40',1),
(3,'5',''     ,1),
(4,'6',NULL   ,1),
(5,'7','5'    ,1),
(6,'7','6'    ,1),
(7,'8','10,11',1),
(8,'8','9'    ,1),
(9,'9,10',''  ,1)

SELECT SUM (CASE WHEN (LTRIM(RTRIM(ISNULL(House,''))) <> '' AND LTRIM(RTRIM(ISNULL(FlatNumber,''))) <> '')
                 THEN (LEN(FlatNumber) - LEN(REPLACE(FlatNumber,',', ''))+1)
				 WHEN (LTRIM(RTRIM(ISNULL(House,''))) <> '')
				 THEN (LEN(House) - LEN(REPLACE(House,',', ''))+1)
				 WHEN (LTRIM(RTRIM(ISNULL(FlatNumber,''))) <> '')
				 THEN (LEN(FlatNumber) - LEN(REPLACE(FlatNumber,',', ''))+1)
				 END) As Result
FROM @tblFlats

Output

Result

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

Find and Count all records containing particular character (Comma character) in SQL Server

Company

Explore

Follow us

Contact Us