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!
Advertisement
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