Replacing a specific char in a string

Question

I have a table called Attendance and columns called attendanceStatus and CourseID. The attendanceStatus column is a text type because I wish to be able to change each 0 to a 1 at any given stage. The data is filled with 0's but I wish to change the last 0 in the string to a 1. The data contains 18 0's, so it would be the 18th character that I would need to change.

I feel like I have come close with this but I am willing to listen to changing it completely if I am not close.

UPDATE Attendance
SET attendanceStatus REPLACE = (attendanceStatus, '0', '1')
WHERE CourseID like '2%';

I realize that this code changes all of the 0's to 1's and I only wish to change the 18th 0.

Answer 1

You are storing attendance status using the wrong method. Although I can appreciate why you would store these in a string, it is really cumbersome. How are you going to answer: How many students attended for at least 10 days? How many students were around on day 6? How many students were absent for three days in a row?

The correct method is to have a separate row for each "day" (I'm not sure what the correct units are). It would be something like:

create table StudentAttendance (
    StudentAttendanceId int not null primary key auto_increment,
    StudentId int not null references Student(StudentId),
    CourseId int not null references Courses(CourseId),
    Date date,
    status char(1),
    constraint unq_scd unique (StudentId, CourseId, Date)
);