SQL Server CASE when nvarchar(50) doesn't end in number letter letter

I have a table with > 3million rows but some of the data in one of the columns is wrong - I want to make it NULL when it's wrong.

I want to create a CASE WHEN 'wrong' THEN = 'NULL'. The column data type is nvarchar(50) and each series should end in a number, letter and letter i.e. 1AA, 1AB or 2DA etc etc. Everything that doesn't match the final permutation of number, letter, letter combination I want to convert into NULL.

UPDATE T1
  SET T1.ABC = CASE
     WHEN RIGHT(ABC <> number, letter, letter)
     THEN ABC = 'NULL'
     end

Any thoughts would be greatly appreciated!

Solution 1:

To set invalid columns to NULL, you can use an UPDATE statement with a WHERE clause

UPDATE T1
SET ABC = NULL
WHERE ABC NOT LIKE '%[0-9][A-Z][A-Z]'

The wildcard % represents zero or more characters. Therefore, this LIKE pattern ignores the beginning of the text and tests the 3 last characters of it.