Replace Null and Zeros only with Empty string in SQL Server

PRA

SOLVED CLOSED ACTIVE ANSWERED
User: PRA
Posted: on Oct 30, 2020 11:34 PM
Forum: SQL Server
Answer: 1
Views: 2655

Hi!

I used below script, but its count wrong when quantity is 10 its show 1.

DECLARE @KARTA AS TABLE(
    [Kod] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nchar](20) NULL,
    [Birthday] [datetime] NULL,
    [Job] [nchar](20) NULL
)
 
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Rustam', CAST(N'1987-12-22' AS Date), N'1')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Muhammad', CAST(N'1994-12-08' AS Date), N'1')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Firuz', CAST(N'1998-04-19' AS Date), N'2')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Safar', CAST(N'2008-01-01' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Nurullo', CAST(N'1987-12-22' AS Date), N'1') 
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Safo', CAST(N'2008-01-29' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Samar', CAST(N'2008-01-25' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Samir', CAST(N'2008-01-09' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Sodiq', CAST(N'2008-01-06' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Sadaf', CAST(N'2008-01-08' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Sobir', CAST(N'2008-01-20' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Sitora', CAST(N'2008-01-22' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Sabur', CAST(N'2008-01-04' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Saido', CAST(N'2008-01-07' AS Date), N'3')   
 
SELECT CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25) AS Age,
    REPLACE(COUNT(CASE K.JOB WHEN 1 THEN 1 END),0,'') worker,
    REPLACE(COUNT(CASE K.JOB WHEN 2 THEN 1 END),0,'') student,
    REPLACE(COUNT(CASE K.JOB WHEN 3 THEN 1 END),0,'') puple
FROM @KARTA K
GROUP BY CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25)
ORDER BY CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25)

Result must be:

Age	worker	student	puple
12			10
22		1
25	1
32	2

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

dharmend...

0 2

Reply
ANSWER
User: dharmendr
Replied: on Oct 31, 2020 07:33 AM
Report

Hi PRA,

Refer below query.

SQL

DECLARE @KARTA AS TABLE(
    [Kod] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nchar](20) NULL,
    [Birthday] [datetime] NULL,
    [Job] [nchar](20) NULL
)
  
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Rustam', CAST(N'1987-12-22' AS Date), N'1')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Muhammad', CAST(N'1994-12-08' AS Date), N'1')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Firuz', CAST(N'1998-04-19' AS Date), N'2')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Safar', CAST(N'2008-01-01' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Nurullo', CAST(N'1987-12-22' AS Date), N'1')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Safo', CAST(N'2008-01-29' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Samar', CAST(N'2008-01-25' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Samir', CAST(N'2008-01-09' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Sodiq', CAST(N'2008-01-06' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Sadaf', CAST(N'2008-01-08' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Sobir', CAST(N'2008-01-20' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Sitora', CAST(N'2008-01-22' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Sabur', CAST(N'2008-01-04' AS Date), N'3')
INSERT INTO @KARTA ([Name], [Birthday], [Job]) VALUES (N'Saido', CAST(N'2008-01-07' AS Date), N'3') 

SELECT CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25) AS Age,
    ISNULL(CAST(NULLIF(COUNT(CASE K.JOB WHEN 1 THEN 1 END),0) AS VARCHAR), '') worker,
    ISNULL(CAST(NULLIF(COUNT(CASE K.JOB WHEN 2 THEN 1 END),0) AS VARCHAR), '') student,
    ISNULL(CAST(NULLIF(COUNT(CASE K.JOB WHEN 3 THEN 1 END),0) AS VARCHAR), '') puple
FROM @KARTA K
GROUP BY CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25)
ORDER BY CONVERT(INT,DATEDIFF(DD,Birthday,GETDATE())/365.25)

Output

Age	worker	student	puple
12			10
22		1
25	1
32	2

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

Replace Null and Zeros only with Empty string in SQL Server

Company

Explore

Follow us

Contact Us