1

I've searched for an answer to this here on the boards and elsewhere - I'm guessing this issue has already been resolved, but for some reason, I'm not finding what I need... so, I'll ask and hope this isn't a duplicated question.

What I have is a list of students and employees in a MSSQL database. One of the columns contains a string with the current term (semester) with an identifier for each class that the student is registered for, delimited by ';' for each entry.

What I'm trying to figure out is how to return a value of students who are signed up for more than 4 classes for the current semester. To get a count of all students registered for the current semester, the query is simple:

SELECT COUNT(*) AS Current_Students
FROM UserData
WHERE StuTermClassString LIKE '%2163%'

This works fine to return the total number of students, but I need a way to return a value of the full-time students (those that have more than 11 class hours per semester, which is typically 4+ classes). So I need a way to determine when the count of classes with '%2163%' for a record is > 4. If I haven't explained this well enough, please let me know and I'll expand on it more. Thanks!

Kevin Flynn
  • 65
  • 1
  • 3
  • 11
  • Storing a delimited list in a database is often not a good idea http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – fthiella Sep 03 '15 at 18:20
  • can you give us some more detail about the data format. what you describe could have a number of variations. what are all the class strings that meet your requirements? – Hogan Sep 03 '15 at 18:22
  • Class String Example: 2163RNRS220;2163RNRS220;2163RNRS220;2163RNRS220;2163RNRS220; This would indicate that the student is enrolled in 5 classes (roughly 15 hours, thus a full-time student). Since this exceeds 4 entries for this record, it would be counted in the results of the query. As for the format, the automated (vb/powershell scripts) processes that mange the accounts have no issues breaking apart the string, so there isn't an issue over functionality. This is the way the raw data is imported from PeopleSoft (db2) to our database. – Kevin Flynn Sep 03 '15 at 18:44

2 Answers2

0

Direct answer to your question:

WHERE StuTermClassString LIKE "%2163%;%;%;%;%"<-- assuming the semester indicator is before the class names, and each class name has a semicolon after it.

But, might I suggest a more reasonable DB structure? Instead of having a column in your Student table with a string of ;-delimited class names, you could have a second table of Registrations, where each row has a Student ID (referring to a row in your Student table), a Semester Number, and a single class name. This way you could query for students with more than a certain number of classes by doing:

SELECT student_id FROM Registrations WHERE semester_number = "2163" GROUP BY student_id HAVING COUNT(student_id) > 3

William B
  • 1,411
  • 8
  • 10
  • Your answer does not fit this requirement " count of classes with '%2163%' for a record is > 4. " But I suspect the requirement is wrong. – Hogan Sep 03 '15 at 18:23
  • Can't reference an alias in the `WHERE`. Fix your code please. – TTeeple Sep 03 '15 at 18:29
  • what about a student that has a string like this: "%;%2163%;%;%;%" -- like I said above -- we can't answer without more detail. – Hogan Sep 03 '15 at 18:29
0

Unfortunately handling pseudo-arrays packed into a string in SQL happens.

You will need to create a string splitting function to parse out the individual items in the list string and present them as a table variable.

See: http://sqlperformance.com/2012/07/t-sql-queries/split-strings

There are many ways to do this, but you should end up with a table variable that can use the standard built-in set oriented SQL functions to find what you need.

Rawheiser
  • 1,200
  • 8
  • 17
  • Thanks, Rawheiser - this looks like a good article. I'll read through this more and see if it can get me moving in the right direction. – Kevin Flynn Sep 03 '15 at 19:01