Hi, my dear experts,
The objective is to get a count of test scores for any given school that is above or below certain threshold..
I have created this sample data for my issue. The challenge is on how to handle the data in scores column which is a nvarchar string.
USE [tempdb]
GO
create table TestScores
(Stu_id int primary key,
Scores nvarchar null, --should be nvarchar (20)
School nvarchar null
)
go
insert into TestScores
Select 1, '133', 'ABC' union
Select 2, '135', 'ABC' union
Select 3, '131', 'ABC' union
Select 4, '.', 'ABC' union
Select 5, '--', 'ABC' union
Select 6, '%', 'ABC' union
Select 7, 'a word', 'ABC' union
Select 8, '45', 'ABC' union
Select 9, '199', 'BBC' union
Select 10, '100', 'ABB'
Select * from TestScores
--Select count (scores), School from TestScores group by School
Select count (scores), School from TestScores
Where
--scores < 133
--Msg 245, Level 16, State 1, Line 1
--Conversion failed when converting the nvarchar value '.' to data type int.
--replace (scores, '.','') < 133
replace (replace (replace (replace (scores, '.','0'), '--','0'), '%','0'), 'a word','0') > 133
group by School
My last Select kind of 'worked', but I don't like it. Not just because it is ugly, but also it will break in the future if other un-account for text get into the field.
What is the right and elegant way to handle this? Controlling how data get in there is not an option.
Thanks!