如何在 SQL 查询中将个位数零'0'(仅一位数字)替换为字符串值

Question

我想用字符串值N/a替换零'0'。我已经使用了一个替换函数，但替换函数的问题是，它在数字值(例如20到2N/a(中每次出现时都用字符串替换0。我想用个位数值"0"替换字符串。

我正在使用当前具有替换功能的查询，如下所示：

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

上述查询的结果如下：

>NSPP012110N/A>

sem2	通过
NSPP01	2110	2N/A	19	17
NSPP02>
NSPP01
NSPP02/td>

替换代码"个位数零"；N/A是通过同时使用IsNULL(NULLIF(((函数。

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

解释：

1. select Count(studentid(--在studentid coulname中的记录计数结果。可以是个位数"；0"；或"；00">

2. 选择CAST(0作为char(20((-将计数函数int的结果转换为char，然后将结果与char匹配"；0"；在下一步中

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

3. SELECT NULLIF(CAST(count(s.studentid(AS char(20((，"0"(--NULLIF；0"；它是硬编码的更多关于NULLIF的解释如下：

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(表达式，替换(---如果表达式为NULL，则ISNULL函数将替换为在我们的情况下为N/a的替换，或者如果值不为NULL，则它将保留该值

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.٭٭