SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE my_error (wrong VARCHAR(50));
INSERT INTO my_error VALUES ('Addiction Advice Services Ltd.');
INSERT INTO my_error VALUES ('AIR BP_and-mistake');
INSERT INTO my_error VALUES ('AIR New Zealand Airlines');
3 rows affected

SELECT * FROM my_error
ORDER BY wrong;


wrong


Addiction Advice Services Ltd.

AIR BP_and-mistake

AIR New Zealand Airlines


CREATE VIEW my_error_view AS 
SELECT 
  CASE
    WHEN UPPER(wrong) LIKE 'ADDICTION ADVICE%' THEN 'ADDICTION ADVICE'
    WHEN UPPER(wrong) LIKE 'AIR BP%'  THEN 'AIR BP'
    WHEN UPPER(wrong) LIKE 'AIR NEW Z%' THEN 'AIR NEW ZEALAND'
    ELSE '***ERROR****'
  END AS wrong
FROM my_error;
SELECT * FROM my_error_view
ORDER BY wrong;


wrong


ADDICTION ADVICE

AIR BP

AIR NEW ZEALAND