Best way to update the same column without using x update statements (if possible)?

I have a few questions regarding the following scenario –

Consider this table (db<>fiddle here):

create TABLE StudentClass( 
name [nvarchar](30) NULL,
class [nvarchar](100) NULL,
)

insert into StudentClass
values ('Anne', 'Math;Art;Art History'),
('Bill', 'Math;English'),
('Charlie', 'English'),
('Daisy', 'English;Art;Art History'),
('Eddy', 'Math;Art')

I want to replace each class subject with a number code. For example:

Math --> 001
English --> 002
Art --> 003
Art History --> 004

If I try to do this in one update statement, it won’t let me do that. The only thing I could think of was four separate statements using replace:

update StudentClass set class = replace(class, 'Math', '001')
update StudentClass set class = replace(class, 'English', '002')
update StudentClass set class = replace(class, 'Art', '003')
update StudentClass set class = replace(class, 'Art History', '004')

This almost works, but not quite. Since “Art History” contains “Art” in it, this update update StudentClass set class = replace(class, 'Art', '003') triggers first and messes up the next update.

My 2 questions are:

First, is there a better way to update the same column instead of making x update statements?

And second, is there a better way to make sure that the Art/ArtHistory replacement doesn’t happen? For this, the only thing I could think of was switching the query order so that it looks like this, but I assume there’s a better way I don’t know about:

update StudentClass set class = replace(class, 'Math', '001')
update StudentClass set class = replace(class, 'English', '002')
update StudentClass set class = replace(class, 'Art History', '004')
update StudentClass set class = replace(class, 'Art', '003')

Appreciate all the help as always!

Answer

In most cases you will probably have a table with the information about the numbers of the classes

In this case you can use JOIN in order to get the result that you need without manually write the names of the options in your query.

With that being said, using small number of consist list of classes numbers and if you do not have that information in table you can use simply CASE statement. THIS MEAN THAT YOU COUNT ON THE ORDER OF THE OPTIONS (when… Then…) in the CASE STATEMENT. This option in this specific scenario is better since you do not need to parse the content of the text.

If you need more flexiblke solution then you can use something like bellow (which will cost more since we parse the content of the text (split it and aggregate) but provide more flexible solution which might fit your needs

;With MyCTE AS (
    SELECT s.name, s.class, V.Num
    FROM StudentClass s
    CROSS APPLY string_split(s.class, ';') ca
    LEFT JOIN (VALUES (N'Math', '001'), (N'English', '002'),(N'Art', '003'),(N'Art History', '004')) V  (Class, Num) ON V.Class = ca.value
) 
SELECT [name], class, STRING_AGG(Num, ';')
FROM MyCTE
GROUP BY [name], class

Note! since I do not have table, I am using this part (VALUES (N'Math', '001'), (N'English', '002'),(N'Art', '003'),(N'Art History', '004')) V (Class, Num) in order to have the values in tabular structure on the fly