MySQL 5.7

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

select version();


version()


5.7.35


create table cars( number_plate varchar(15), colour varchar(15), name  varchar(15), flag  varchar(15) )
insert into cars
values('KA01AA0101','Black', 'Ford', 'UK'),('KA01AA0102','White', 'Ford', 'US')
select * from cars


number_plate
colour
name
flag


KA01AA0101
Black
Ford
UK

KA01AA0102
White
Ford
US


select number_plate, colour, concat(name,' ',flag) as brand_name from cars


number_plate
colour
brand_name


KA01AA0101
Black
Ford UK

KA01AA0102
White
Ford US


select * from (select distinct  number_plate, colour, concat(name,' ',flag) as brand_name from cars) a where brand_name like '%UK%'


number_plate
colour
brand_name


KA01AA0101
Black
Ford UK