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

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!

CodePudding user response：

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.

CodePudding user response：

Thanks for the help. Combination of answers has helped me.

  UPDATE T1
  SET T1.ABC = REPLACE(RIGHT(RTRIM(ABC ), LEN(RTRIM(ABC ))-3), ' ','')
  WHERE ABC LIKE '%[0-9][A-Z][A-Z]'

And the rest have been left as NULLs

CodePudding user response：

adding another possible method without using regex

UPDATE T1
  SET T1.ABC = CASE
                   WHEN try_cast(left(col1,1) as int)<> null and try_cast(left(right(col1,2),1) as int) = null and try_cast(right(col1,1) as int) = null 
     THEN T1.ABC = T1.ABC
                else ABC = NULL
     end

The above method will update all the records which is time-consuming and a heavy load on the server. Rather we can filter the records and then update them.

UPDATE T1 SET T1.ABC = NULL where
CASE WHEN try_cast(left(col1,1) as int)<> null and try_cast(left(right(col1,2),1) as int) = null and try_cast(right(col1,1) as int) = null THEN 1 else 0 end = 1