How to replace single digit zero '0' (one digit only) with a string value in SQL Query

Question

I want to replace zero '0' with a string value N/A. I already used a replace function but the problem with replace function is that it replaces 0 with a String at every occurrence in digit value (e.g 20 to 2N/A). I want to replace a string with the single-digit value '0'.

I am using the current query with replace function as below:

select left(pdc.semester,6) as Sem1,RIGHT(pdc.semester,4) as sem2,pdc.semester as Semester,replace(count(pdc.CNIC),'0','N/A') as TotalRegistrations,replace(count(s.studentid),'0','N/A') as TotalRegistered,
Replace(count(case when r.Grade is not null then 'Appeared' end),0,'N/A') as Appeared,
Replace(count(case when r.Grade='f' then 'fail' end),'0','N/A') as Failed,
Replace(count(case when r.grade <>'f' then 'pass' end),'0','N/A') as Passed

from PDC_PreRegistration pdc 
left join  Students s on pdc.Semester=s.CurSemester and s.nic=pdc.CNIC
left join studentresultnet r on s.studentid=r.studentid and s.CurSemester=r.Semester 
left join semester se on se.Semester=r.Semester
where pdc.semester 
in (select CurSemester from students where  batch='PD2110') 
group by pdc.Semester
ORDER BY  sem2,Sem1

The Result of the above query is as follows:

Sem1	sem2	Semester	TotalRegistrations	TotalRegistered	Appeared	Failed	Passed
NSPP01	2110	NSPP01 2110	2N/A	2N/A	19	2	17
NSPP02	2110	NSPP02 2110	37	35	24	1	23
NSPP01	2111	NSPP01 2111	76	42	N/A	N/A	N/A
NSPP02	2111	NSPP02 2111	121	81	N/A	N/A	N/A

I want to replace '0' with N/A but not 20 to 2N/A.

SQL Query without Replace function produces the following result.

select left(pdc.semester,6) as Sem1,RIGHT(pdc.semester,4) as sem2,pdc.semester as Semester,count(pdc.CNIC) as TotalRegistrations,count(s.studentid) as TotalRegistered,
count(case when r.Grade is not null then 'Appeared' end) as Appeared,
count(case when r.Grade='f' then 'fail' end) as Failed,
count(case when r.grade <>'f' then 'pass' end) as Passed

from PDC_PreRegistration pdc 
left join  Students s on pdc.Semester=s.CurSemester and s.nic=pdc.CNIC
left join studentresultnet r on s.studentid=r.studentid and s.CurSemester=r.Semester 
left join semester se on se.Semester=r.Semester
where pdc.semester 
in (select CurSemester from students where  batch='PD2110') 
group by pdc.Semester
ORDER BY  sem2,Sem1

Result of the above query

Sem1	sem2	Semester	TotalRegistrations	TotalRegistered	Appeared	Failed	Passed
NSPP01	2110	NSPP01 2110	20	20	19	2	17
NSPP02	2110	NSPP02 2110	37	35	24	1	23
NSPP01	2111	NSPP01 2111	76	42	0	0	0
NSPP02	2111	NSPP02 2111	121	81	0	0	0

Answer 1

Code to replace "single-digit zero" with N/A is by using IsNULL(NULLIF()) functions together.

IsNULL(NULLIF(CAST(count(s.studentid) AS char(20)),'0'), 'N/A')

select left(pdc.semester,6) as Sem1,RIGHT(pdc.semester,4) as sem2,pdc.semester as Semester,IsNULL(NULLIF(CAST(count(s.studentid) AS char(20)),'0'), 'N/A') as TotalRegistrations,count(s.studentid) as TotalRegistered,
Replace(count(case when r.Grade is not null then 'Appeared' end),0,'N/A') as Appeared,
Replace(count(case when r.Grade='f' then 'fail' end),'0','N/A') as Failed,
Replace(count(case when r.grade <>'f' then 'pass' end),'0','N/A') as Passed

from PDC_PreRegistration pdc 
left join  Students s on pdc.Semester=s.CurSemester and s.nic=pdc.CNIC
left join studentresultnet r on s.studentid=r.studentid and s.CurSemester=r.Semester 
left join semester se on se.Semester=r.Semester
where pdc.semester 
in (select CurSemester from students where  batch='PD2110') 
group by pdc.Semester
ORDER BY  sem2,Sem1

Explanation:

1. select Count(studentid) --Results in count of records in studentid coulmn name.which can be single digit "0" or "00"

2. select CAST(0 as char(20)) --Casting result from Count Function int to char and then matching result with char "0" in Next step.

SELECT  CAST(count(s.studentid) AS char(20)) 

3. SELECT NULLIF(CAST(count(s.studentid) AS char(20)),'0') --NULLIF(expr1, expr2).Here expr1 will come from SQL COUNT function as char and expr2 is char "0" which is hardcoded. More Explanation of NULLIF is as follows:

select NULLIF('abc','abc') --**Results Null because both expressions inside NullIF have same value.٭٭

select NULLIF('0','0') --**Result Null because both Expressions inside NullIF have same value.٭٭

select NUllIF('20','0') --** Results 20 becuase Expressions inside NullIF have not same value.٭٭

4. ---ISNULL (expression, replacement)--- if the expression is NULL then ISNULL function will replace with a replacement which is N/A in our case, or if the value is not null then it will retain the value.

select ISNULL(NULL,' Value is NULL so replace by This expression') --**Results in Replace Expression.٭٭

select ISNULL('Any value', 'if the value is null then replace by This expression') --**Results in Any value.٭٭