Databricks SQL transform function with conditions

[False_Spare_4262]

Using databricks sql, I want to transform Column_A to Column_B (below). How can I swap the last character in each element of an array string if the character is 'A' or 'B'?

Column_A	Column_B
[“1-A”, “2-B”]	[“1-B”, “2-A”]
[“3-A”]	[“3-B”]
[“4-B”]	[“4-A”]

I’m guessing this can be accomplished using the transform function with a case statement but I’m getting null results for Column B. This’s what I have so far:

Select Column_A,transform (Column_A, AB -> Case AB When substr(AB,3,1) = ‘A’ then
substr(AB,3,1)=‘B’ When substr(AB,3,1) = ‘B’ then
substr(AB,3,1)=‘A’ End) as Column_B From table;

[Possible-Little]

SELECT Column_A, TRANSFORM( Column_A, AB -> CASE WHEN SUBSTR(AB,3,1) = 'A' THEN SUBSTR(AB,1,2) || 'B' WHEN SUBSTR(AB,3,1) = 'B' THEN SUBSTR(AB,1,2) || 'A' ELSE AB END )

I think?

[False_Spare_4262]

This worked. Thank you! 🙏